VBA Code to Split Master Data into multiple workbooks by Row Count

[KaranM]

Hi,

I'm looking for a VBA Code that can help me to split master data into multiple workbooks by row count.

The sheet has about 50k rows and needs to split by 150 Rows into each workbook

Row 1 to 5 is basically the header and needs to be there in every sheet including the formatting & spacing.

Row 6 onwards is where the data starts and needs to be split into batches of 150 rows
For Example
Row 6-155 = 150 Rows split into a new workbook with the header from Row 1 to 5
Row 156-305 = 150 Rows split into a new workbook with the header from Row 1 to 5

and so forth

Any help would be appreciated.

Thank you.

 

[Yongle]

not yet

 

[KaranM]

Ok.

Thank you

 

[Yongle]

I still do not know what you want
Until I know I cannot help

Which sheet is your "data" - is it the first sheet ?
Which sheet is the correct "Results" sheet ?

Please use the first 6 rows of your "data" and create "Results" sheet manually using the same values
- I need to be able to see both with the same values to understand what you want

 

[KaranM]

Hi

I have updated the folder to include the master data workbook as well as 4 other workbooks that shows how the results need to be. (Workbooks with names containing 001 / 002 / 003 / 004 and so forth)

MrExcel - Google Drive

drive.google.com

Thank you

 

[Yongle]

The only thing I can see that is different is that the individual workbooks had 2 sheets
Code below amended to delete the 2nd sheet and rename sheet with data as Sheet1

This replaces previous version of SaveToWorkBook

Private Sub SaveToWorkBook(Temp As Worksheet, aName)
    Const SavePath = "C:\folder\subfolder"
    Dim wb As Workbook
    Set wb = Workbooks.Add
    Temp.Copy Before:=wb.Sheets(1)
    Application.DisplayAlerts = False
    wb.Sheets(2).Delete
    wb.Sheets(1).Name = "Sheet1"
    Application.DisplayAlerts = True
    wb.SaveAs SavePath & "\" & aName
    wb.Close False
End Sub

If that does not work ...
Perhaps there are instructions somewhere telling you to submit the workbook with extension .xls or .csv
Let me know - it requires 1 line in code to be amended

 

[KaranM]

Hey

Been trying to figure out the above & finally realized the problem by doing it manually but can't figure it out in code form.

For the platform to accept the generated files the name of the sheet in the workbook cannot be changed and needs to be the same with the one in the master data workbook.

The name of the workbook on the other hand doesn't matter.

Thank you again for your help.

 

[Yongle]

Is there only one sheet in the master workbook?

 

[KaranM]

Correct, there is only a single sheet in the master workbook

 

[Yongle]

The name of the sheet needs passing as a variable to the other sub

amend these lines in procedure SeparateWorkbooks

Set wS = ThisWorkbook.Sheets("MasterData")
Set wS = ThisWorkbook.Sheets(1)

Call SaveToWorkBook(Temp, aName)
Call SaveToWorkBook(Temp, aName,ws.name)

Replacement sub procedure

Private Sub SaveToWorkBook(Temp As Worksheet, aName As String,wsName As String)
    Const SavePath = "C:\folder\subfolder"
    Dim wb As Workbook
    Set wb = Workbooks.Add
    Temp.Copy Before:=wb.Sheets(1)
    Application.DisplayAlerts = False
    wb.Sheets(2).Delete
    wb.Sheets(1).Name = wsName
    Application.DisplayAlerts = True
    wb.SaveAs SavePath & "\" & aName
    wb.Close False
End Sub

 

[KaranM]

Works exactly as intended now.

Once again... thank you for all the help... much appreciated!