Excel VB Code Fix To Duplicate Rows from Master to New Workbooks


The attached sample has a code that breaks out information into separate workbooks based on changes in column C.  The problem I am dealing with is a sub c code exception that has been added which is missing some rows when its created and saved in a file.  For example, if the number in column c is 000060 and the manager is Washington, George, a new workbook is created named 000060A.

Based on the current code this works fine but when you open 000060A it is missing rows 1-13 that appear in the master.  The Sub c code at the bottom calls out the SR =14 which is set to the number of rows with first line after header but this is not sufficient as I mentioned earlier because its missing rows 1-13 from that master sheet.  If I change the SR to equal 1 (thinking that would grab everything), it bypasses the Sub c code.

So basically when you open 000060A the first 14 rows should look identical to opening workbooks 000030, 000002, etc. (see sample pic).  Please note that if you are going to test the existing code you'll need to provide a file path of where you want the new workbooks to be saved (Filepath = "S:\").

I've been stuck with this issue for a while and hoping some VB wizards can provide assistance.  Please provide a sample of the updated code in an attached workbook.  Thank you!
Who is Participating?
Ejgil HedegaardConnect With a Mentor Commented:
Try attached.
Both SR and SO must be 14 to start the number search and the line copy after the title rows 1:13.
To copy the first 13 rows the title copy line is
wks.Rows("1:13").Copy wb.Sheets(1).Rows("1") 'Title
Fabrice LambertFabrice LambertCommented:
Wouldn't it be simplier, faster and more reliable to:
- Have a pattern worksheet hidden in the "master workbook".
- Create a new workbook
- Copy the pattern worksheet.
- Copy the datas.
- Save the newly created workbook.

And please, when uploading files, deactivate any code touching to Excel's environment (screen update / cutcopymode ect ...), our computer isn't your !
Also, deactivate any password if possible, help us helping you.
EscanabaAuthor Commented:
Fabrice - I've been with Experts Exchange for several years with multiple questions and, while I do realize we are not working off the same computer, this is the first that some has asked portions of the code to be deactivated.  I'll keep that in mind.  In addition, the password is in the code but again I'll keep that in mind.

On to your recommendation - this macro automation was requested and unfortunately I am not that skilled in VB so this has been a combination of support from other helpful members of this community.  As it stands, running the existing code breaks everything apart within seconds compared to the manual process that was being used prior.  If what you are recommending is a completely new VB code I would not know enough to recreate based on your process outline.  As this entire project is near the finish line and I am content with the results beyond this one aspect of the rows not carrying over I'd like to stick with what I have and just get insight on how to edit the existing code.
Fabrice LambertFabrice LambertCommented:
From what I saw after executing step by steps (F8 hotkey):

The function c_000060 create a workbook (without overheads) with rows based on the criteria provided.
Rows from the master are removed.

After calling 000060 3 times, the function CreateNewWorkbooks filter the remaining rows and save them in additional workbooks (with overheads).

Total 7 workbooks are created:
3 from the c_000060 function
4 from the CreateNewWorkbooks function.

Do you want the c_000060 function to create workbooks with overheads ?
If so, the function need a little change:
SR = 14 'Correct if structure will be changed
'// SO = 2
SO = 14
Do While wks.Cells(SR, 3) <> ""
    If wks.Cells(SR, 3) = No And wks.Cells(SR, 11) = name Then
        If WG Then
            Set wb = Workbooks.Add
            WG = False
            '// wks.Rows("13").Copy wb.Sheets(1).Rows("1") 'Title
            wks.Range("1:13").Copy wb.Sheets(1).Range("1:13")
        End If

Open in new window

EDIT: Ejgil figured it out while I was writing this.
EscanabaAuthor Commented:
Thank you!!!  This was very helpful!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.