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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Ejgil HedegaardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.