Create New Workbooks from a list.

[Leelabird]

Good evening All,

I have a somewhat sticky problem at work with a very untidy and large workbook which I inherited from a previous Admin employee. The workbook is a general mess. Many formulas are missing creating gaps in the data, data is incorrectly placed etc. etc. etc...

I'm in the process of tidying the whole thing up which so far has being going well. However, part of the new book-keeping process that I wish to implement will require new workbooks to be created from a list of employees in column A of sheet1.

Each row (from columns B - P) has relevant data for each employee listed in column A. An employee may be listed more than once in column A.

What I would like to do is create a new workbook for each employee listed in column A (named after each employee) and within each new individual workbook copy/paste to the first worksheet all the relevant data from the source workbook for the individual employee (i.e. from columns B - P). An employee may have one or more rows of data to be transferred over depending on the number of times he/she is listed in column A of the source workbook.

Each new individual workbook will then be sent to the respective department manager who will then have his/her allocated employees fill in various details for performance evaluations. Confidentiality is the reason behind needing individual workbooks rather than just one workbook with many employee worksheets.

I have attached a de-sensitised sample of the main (source) workbook with just a few rows of data.

There are some notes in the sample also.

Hoping that someone may be able to assist and thank you for taking the time to at least look at my query.

Kind regards,
Leela.

P.S. Also posted here:-
http://www.thecodecage.com/forumz/showt ... p?t=215023" onclick="window.open(this.href);return false;

[Rudi]

Hi Leela,

Welcome to Eileen's Lounge.

Thankyou for the clear instructions and the link to cross-posted information at "the code cage". Appreciate that!

Based on your instructions, give this code a try.
Copy it into a standard module in your source workbook and run it.
The macro assumes the list of employees is on the first sheet in your workbook.
The new workbooks containing the records for each employee is saved into the same folder as the source workbook.

Code: Select all

Sub SplitToNewBook()
Dim wbS As Workbook
Dim shS As Worksheet, shF As Worksheet, shD As Worksheet
Dim rgC As Range, rgF As Range
Dim sFN As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wbS = ThisWorkbook
    Set shS = wbS.Worksheets(1)
    Set shF = wbS.Worksheets.Add(After:=shS)
    Set rgF = shF.Range("A1:A2")
    shS.Range("A1").CurrentRegion.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=shF.Range("A1"), Unique:=True
    Do Until shF.Range("A2").Value = ""
        Set shD = wbS.Worksheets.Add(After:=shS)
        shD.Name = shF.Range("A2").Value
        shS.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rgF, CopyToRange:=shD.Range("A1")
        shD.Range("A1").CurrentRegion.Columns.AutoFit
        shD.Copy
        ActiveSheet.Columns(1).Delete
        sFN = ThisWorkbook.Path & "\" & shD.Name & ".xlsx"
        ActiveWorkbook.SaveAs sFN, 51
        Workbooks(shD.Name & ".xlsx").Close False
        shD.Delete
        shF.Rows(2).EntireRow.Delete
        Set rgF = shF.Range("A1:A2")
    Loop
    shF.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Record groups have been split to workbooks at path: " & vbNewLine & ThisWorkbook.Path, vbInformation
End Sub

[Leelabird]

Hi Rudi,

Firstly: many thanks for the welcome.

Secondly: WOW! Beautiful, just beautiful. It worked seamlessly first time, no error messages at all! The workbooks have all been stored away neatly and flawlessly in the same folder.
I've created a new desktop folder using MkDir just for this aspect of the large tidy up task that I have in front of me and changed the path to suit. It still worked flawlessly and all these new employee workbooks and the source workbook are tucked away neatly in one folder together (that's another task done!).

I'm impressed!!

Thank you Rudi, thank you.
Kind regards,
Leela.

[Rudi]

Glad we could assist.
It does make it easy to help when you provide very clear instructions and a sample of your data. TX.

BTW: It would be helpful to members at the code cage if you put a link to this thread there so they are aware of the replies here. It will help them avoid spending time and effort on providing solutions to a solved question.

[Leelabird]

Hi Rudi,


"BTW: It would be helpful to members at the code cage if you put a link to this thread there so they are aware of the replies here. It will help them avoid spending time and effort on providing solutions to a solved question."

Done!

Many thanks again,
Leela.

[Rudi]

[Leelabird]

Hi Rudi,

I'm back but please don't be afraid!

Everything is working nicely. I've found a little spare time to properly read through the code that you supplied and I was wondering if you could explain some things to me. I'm not one to just copy and paste and be done with it, I prefer to understand what I am working with just in case something comes up in the future so that I can fix it without bothering you kindly people again.

All the declared variables and the values set to those variables is clear enough.

With the following line:-

Code: Select all

shS.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rgF, CopyToRange:=shD.Range("A1")

the advanced filter has been placed on the CurrentRegion starting at A1. Does the CurrentRegion refer to the whole data set?
Is the advanced filter referencing the whole data set with the criteria(rgF) the range it needs to "pin-point"?

With the following line:-

Code: Select all

sFN = ThisWorkbook.Path & "\" & shD.Name & ".xlsx"

is Path &"\" a "shorthand" version of the full file path directly referencing, in this case, the same folder as the source work book without having to type out the whole file path?

With the following line:-

Code: Select all

ActiveWorkbook.SaveAs sFN, 51

is 51 just a numerical value for the file type (.xlsx)?

Please reply at your leisure, when you have time. Its not Doomsday stuff!. These are just things that I would like to store away at the back of my memory.

Many thanks again Rudi.

Kind regards,
Leela.

[Rudi]

Hi Leela,

Your questions are welcome.
The ideal purpose of the lounge is to help loungers learn and become self empowered. This in turn will help you to help others here (if you so please).

To answer your questions:

1. CurrentRegion does indeed refer to the whole data set. It expands out in all directions from the active cell to end with a block of data. Only blank rows and columns determine where it stops. See this page for some extra clarity.
       
2. The variable (rgF) refers to range (A1:A2) on the sheet dedicated to supplying the criteria to filter. Each pass of the loop filters the main list (currentregion) based on the criteria determined in A1:A2 on the criteria sheet. To understand the advanced filter, you must be aware that the first item in the criteria (rgF) determines which column to filter on (in this case "Employee Name") and the next item (or items) determines the values to filter. The loop ends with deleting row 2, meaning that the next iteration uses the heading "Employee Name" (again to determine the column to filter on) and the next name in the list. (And so it continues until all the names in the criteria list have been deleted).
       
3. ThisWorkbook.Path returns a String that represents the complete path to the workbook/file that this workbook object represents. So yes, your analysis is correct. Its a useful way of accessing the entire path of the workbook object.
       
4. Sorry about that. I took a shortcut (which is not very clear for anyone trying to reference the code). When you use SaveAs (with these newer XML file formats), you need to not only supply the path to save to, but you also need to supply the FileFormat argument. The value of the argument can be passed as a variable, a named constant or by its index. I just used the index (which happens to be 51), but as you have noticed, it does not add a lot of legibility to the code. The named constant in VBA is xlOpenXMLWorkbook, which is a bit more clarifying. For a good explanation of this argument, see this article by Ron de Bruin, MVP.

If you need any more explanation, just ask.

[Leelabird]

Hi Rudi,

Thank you again for your time.

I'm glad that I wasn't too far off in my interpretation! Your explanations clarified a number of things for me.

As for your reply no.4, there is no need to apologise. I would assume that such abbreviations would make codes more efficient. I had an inkling of what you were up to.

Others that may come by this way in the future looking for similar resolutions will be most fortunate to be able to understand your code with these explanations.

Keep up the good work Rudi.

Kind regards,
Leela.

[Rudi]

Its a pleasure to assist.
Have a great day!