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]

Welcome to the forum
- this creates the separate workbooks
- row numbers are embedded in their names

For the first test ...
1. Test on a COPY of your workbbok
2. Place code in a NEW module
3. Amend name of sheet containing master data
Set wS = ThisWorkbook.Sheets("MasterData")
- amend path to save workbooks
Const SavePath = "C:\folder\subfolder"

Amending ...
If you need any help amending the individual workbook names etc let me know

Option Explicit

Sub SeparateWorkbooks()
    Dim wS As Worksheet, Temp As Worksheet, Hdr As Range, Data As Range, aName As String
    Dim LastRow As Long, LastCol As Long, r As Long, r2 As Long
    Const H = 5, NoRows = 150
    Application.ScreenUpdating = False
    Set wS = ThisWorkbook.Sheets("MasterData")
    LastCol = wS.Cells.Find("*", wS.Cells(1, 1), xlFormulas, xlWhole, xlByColumns, xlPrevious).Column
    LastRow = wS.Cells(Rows.Count, 1).End(xlUp).Row
   
'add temp sheet with headers and formating
    wS.Copy Before:=Sheets(1)
    Set Temp = Sheets(1)
    Set Hdr = Temp.Rows(1).Resize(H, LastCol)
    Set Data = Hdr.Offset(5).Resize(NoRows)
    Data.ClearContents
    Data.Offset(NoRows).Resize(Rows.Count - H - NoRows).EntireRow.Delete
   
'copy the data to temp sheet and save to workbook
    For r = 6 To LastRow Step NoRows
        wS.Rows(r).Resize(NoRows, LastCol).Copy Data
        r2 = WorksheetFunction.Min(LastRow, r + NoRows)
        aName = "Rows " & Format(r, "00000") & " to " & Format(r2, "00000")
        Call SaveToWorkBook(Temp, aName)
    Next r

'delete temporary sheet
    Application.DisplayAlerts = False
    Temp.Delete
    Application.DisplayAlerts = True
End Sub


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)
    wb.SaveAs SavePath & "\" & aName
    wb.Close False
End Sub

 

[KaranM]

Thank you.

Tried this and it works.

However the split files are to be fed into a platform using mass upload and somehow it says format not valid.

I don't mind sending you the original file to you personally if that would be ok for you to have a look.

Let me know.

Once again thank you.

 

[Yongle]

I will send updated code later today with output to a different formats for you to test

 

[KaranM]

Thank You

Here is a copy of a blank template for your idea...

MrExcel - Google Drive

drive.google.com

 

[Yongle]

However the split files are to be fed into a platform using mass upload and somehow it says format not valid.

I misunderstood your problem - I thought you meant you needed a different Excel format
That is not correct - you need a different layout


I do not understand what you now want
The linked workbook contains no explanations

If you want any further help, please supply the following ...
- a small sample and layout of the ORIGINAL master data
- layout of expected results using the same data

 

[KaranM]

Hi

My bad for the confusion

Please check the following link

MrExcel - Google Drive

drive.google.com

I have updated the file with a sample data with 1000 rows (Data starts from Row 6)

The file is the original template file provided by the platform & have filled in the sample data for your reference

The platform has an upload limit of 150 SKU per file, hence the reason for needing each file to only have 150 SKU.
Each workbook needs to be in the same exact format for the platform to accept the file, otherwise, it keeps on saying format not valid.


Thank you

 

[Yongle]

thanks - will look at you workbook with the next 48 hours and update this thread after that

 

[KaranM]

Thanks

 

[KaranM]

Hi

any updates on this..

thanks