New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Needing A macro for splitting supervisors records

Macros, VBA, Excel Automation, etc.

Needing A macro for splitting supervisors records

Postby TonyC » Tue May 31, 2016 3:52 pm

DEAR Excel Experts,
Your help with the below macro is greatly appreciated in order to save great time for my payroll processing.
The attached sheet has a bunch of employees whom are missing certain criteria’s in which I need to send an email to each employee supervisor certain notifications.

Therefore, the major player in this sheet is going to be the supervisor name (which I highlighted in red).

I need a macro, where I want each supervisor to split out on to his own sheet or file or tab ( whatever works) with his or her corresponding data fields such as –Supv ID, Empl ID, Name, Action, Job code, etc.. (All the fields highlighted in green). This way I will have a file for each supervisor in which therefore, I can send emails to.

So for instance, some supervisors like (John do) will only pull one record, but some supervisors like (John shee) will pull two records and so on. Just as if you would filter under each supervisor.

Thank you all so much in advance
Tony
  • 0

You do not have the required permissions to view the files attached to this post.
TonyC
Rookie
 
Posts: 13
Joined: Dec 17, 2015
Reputation: 0
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby vcoolio » Wed Jun 01, 2016 7:53 am

Hello Tony,

See if the following code does the task for you:-

Code: Select all
Option Explicit
Sub CreateSheetsCopyData()

Application.ScreenUpdating = False

        Dim ar As Variant
        Dim i As Integer
        Dim LR As Long
        Dim c As Range
        Dim ws As Worksheet
       
LR = Range("A" & Rows.Count).End(xlUp).Row
ar = Sheet1.Range("A3", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))

For Each c In Range("A3:A" & LR)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
        End If
  Next c
 
Sheet1.Select
  For i = LBound(ar) To UBound(ar)
         Sheets(ar(i, 1)).UsedRange.ClearContents
         Range("A2", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         Range("A2", Sheet1.Range("N" & Rows.Count).End(xlUp)).Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(xlUp)
         Sheets(ar(i, 1)).Columns.AutoFit
         Sheets(ar(i, 1)).Columns.WrapText = False
    Next i
[A2].AutoFilter
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Sheets created/data transfer completed!", vbExclamation, "Status"

End Sub


The code creates new sheets for each individual (filters on Column A) and transfers the relevant data to each individual's sheet.

Your work book, with code installed, is attached. Click on the button to see the code at work.
I've fiddled a bit with your formatting just to make it a little more legible.
Test the code in a copy of your actual work book first.

I hope that this helps.

Cheerio,
vcoolio.
  • 1

You do not have the required permissions to view the files attached to this post.
vcoolio
Rookie
 
Posts: 18
Joined: Jan 31, 2015
Reputation: 2
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby TonyC » Wed Jun 01, 2016 3:31 pm

Thank You VCoolio...
God Bless
Tony
  • 0

TonyC
Rookie
 
Posts: 13
Joined: Dec 17, 2015
Reputation: 0
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby vcoolio » Wed Jun 01, 2016 5:27 pm

Hello Tony,

You're welcome. Glad that I could help.

One little thing that you may want to keep an eye out for. What if you have, say, two Supervisors with the same name? You may have two John Smiths or two Carol Jones. Excel won't allow two sheets with the same name so in such a situation, a simple fix would be to add an extra letter to the name of one or the other in Column A or even a numeric value such as John Smith2. Otherwise, all details for both John Smiths will go into the one work sheet.

Test the scenario if you like in a copy of your work book, just in case.

It may never happen but if you are not sure, just call back.

Cheerio,
vcoolio.
  • 0

vcoolio
Rookie
 
Posts: 18
Joined: Jan 31, 2015
Reputation: 2
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby TonyC » Wed Aug 10, 2016 8:26 am

Thank you so much again Vcoolio..
The code that you provided me with made my life a lot easier.

Currently what I am trying to do is to use the same example that i supplied earlier. However, this time instead of dividing the managers per tab, I was wondering if it's possible to devide them on seperate workbooks? so basically, each manager will get his own sperate workbook with his assigned employees - the same concept as the tabs one but this time in workbooks/ per manager instead of tab/ per manager.

The goal is to save these workbooks to a predefined location under each manager's name, so i can finally be able to email each manager his own workbook; which is really the ultimate goal.

Thank you again for the help.

Tony
  • 0

TonyC
Rookie
 
Posts: 13
Joined: Dec 17, 2015
Reputation: 0
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby vcoolio » Fri Aug 12, 2016 1:51 am

Hello Tony,

I'll have a look at this for you tomorrow. Hang in there!!

Cheerio,
vcoolio.
  • 0

vcoolio
Rookie
 
Posts: 18
Joined: Jan 31, 2015
Reputation: 2
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby vcoolio » Sun Aug 14, 2016 7:05 am

Hello Tony,

Sorry that I haven't returned sooner. Time got away from me.

I was rummaging around various forums a short time ago looking for posts that I could help with when I accidently came across the following query:-

http://www.eileenslounge.com/viewtopic. ... 04#p188904

I'm sure that its pretty well the same scenario as yours so give the code in the link a try (it will save me having to repeat myself!).

If you get stuck with any aspect of it, just come back here and I'll help you sort it out.

The author of the code in the thread above is a real Guru so I'm sure that you won't have any issues at all.

Let me know how it goes.

Cheerio,
vcoolio.
  • 0

vcoolio
Rookie
 
Posts: 18
Joined: Jan 31, 2015
Reputation: 2
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby TonyC » Fri Aug 19, 2016 11:27 am

Hello Vcoolio,

Thank you so much again. This is exactly the idea that I am looking for.

However, i applied the same code to my sheet , but it did not give me the same results as it did for her sheet.
could it be that my ranges needs to be adjusted?

Thank you again
  • 0

TonyC
Rookie
 
Posts: 13
Joined: Dec 17, 2015
Reputation: 0
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby vcoolio » Fri Aug 19, 2016 6:40 pm

Hi Tony,


However, i applied the same code to my sheet , but it did not give me the same results as it did for her sheet.
could it be that my ranges needs to be adjusted?


Could you let me know where, what or how its not working for you please.

Cheerio,
vcoolio.
  • 0

vcoolio
Rookie
 
Posts: 18
Joined: Jan 31, 2015
Reputation: 2
Excel Version: 2010

Re: Needing A macro for splitting supervisors records

Postby TonyC » Mon Aug 22, 2016 10:59 am

Hey Vcoolio,

I basically copied the code to my sheet and ran it, and it did not give me the same out put to my documetns folder as it did on her sheet. There was nothing.

Have you applied the code to my sheet? were you able to get any outpput?

Thanks again
  • 0

TonyC
Rookie
 
Posts: 13
Joined: Dec 17, 2015
Reputation: 0
Excel Version: 2010

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 7 guests