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.

Re: Needing A macro for splitting supervisors records

Postby vcoolio » Mon Aug 22, 2016 6:33 pm

Hello Tony,

The reason, I believe, that it didn't quite work for you is that your data (as per the earlier sample that you supplied) starts on row 3 whereas the code from the other forum has it starting in row 2, as per the Poster's sample. So changing the cell references will do the trick for you.

However, I've attached a copy of your original sample with the code from the other forum slightly modified. It works nicely. You'll notice that I've reduced the size of the file as we only need a few rows of data to test it. The modified code is as follows:-

Code: Select all
Sub CreateNewWbks()

    Dim dic As Object
    Dim rng As Range
    Dim ws As Worksheet
    Dim mypath As String
    Dim lr As Long
    Set dic = CreateObject("scripting.dictionary")
    Set ws = Sheet1
   
mypath = ThisWorkbook.Path & "\"
lr = ws.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With ws
   
For nr = lr To 3 Step -1
        If (Not dic.exists(.Cells(nr, "A").Value)) Then
        dic.Add .Cells(nr, "A").Value, .Cells(nr, "A").Value
        Set rng = .Range("A2:N" & .Cells(Rows.Count, 1).End(xlUp).Row)
            rng.AutoFilter 1, .Range("A" & nr).Value
            rng.Copy
            Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.Columns.AutoFit
            ActiveWorkbook.SaveAs Filename:=mypath & .Range("A" & nr).Value & ".xlsx"
            ActiveWorkbook.Close
        End If
    Next
           .AutoFilterMode = False
End With

MsgBox "Done!", vbExclamation
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


When the messagebox comes up with the message "Done", you'll know that all is ready for you. It will take a few seconds as there is a lot of work for the code to do.

The new work books will be stored in the same folder as the source file.

I hope that this solves it for you.

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 Aug 31, 2016 3:23 pm

Thank you very Much Vcoolio
  • 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 Aug 31, 2016 8:52 pm

You're welcome Tony. Glad that I could help

You could take this a little further and create a search box for the file that you'd like to bring up. It could be handy especially if your list of work books is long or may become longer.

You could create a drop down list of all Supervisors names in a cell somewhere in your main sheet in your main work book and then, once the name is selected, click on a button to open that particular work book. A code as follows would work:-


Code: Select all
Sub FindTheSupervisor()

Application.ScreenUpdating = False

    Dim stgF As String
    Dim stgP As String
    Dim wb As Workbook

stgP = "C:\Users\aaa\" '------> Place your file path here.
stgF = Dir(stgP & "\*.xlsx")

On Error Resume Next

Do While stgF <> vbNullString
    Sheets("Input").Select  '------->Change sheet name to suit yourself.
    stgF = Range("C18")  '--------Change the cell reference to suit yourself.
    Set wb = Workbooks.Open(stgP & "\" & stgF)
stgF = Dir()
Loop

Application.ScreenUpdating = True

End Sub


Assign the code to a button beside the cell with the drop down list and away you go!

I've attached a sample work book but you'll have to change the file path in the code module to suit yourself.

Cheerio,
vcoolio.
  • 0

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

Previous

Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 11 guests

cron