When I run this macro with set up:

Personal.xlsb

C:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART

I only get Files have been split successfully! But NO files have been split or anything. Nothing seems to happened expect for the message box that pops up.




However when I run this macro via .xlsm file it works. It's creating new files and splitting the main file into smaller ones with maximum of 150 rows.



Why is it not working via Personal.xlsb setup?





Sub SplitWorkbookWithHeaders()
Dim ws As Worksheet
Dim newWb As Workbook
Dim rowCount As Long
Dim fileCount As Integer
Dim startRow As Long
Dim endRow As Long
Dim maxRows As Long
Dim headerRow As Range

' Set maximum number of rows per split file (excluding header)
maxRows = 150

' Get the current worksheet
Set ws = ThisWorkbook.Sheets(1)

' Get the total number of rows in the worksheet
rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' Set the header row
Set headerRow = ws.Rows(1)


' Initialize the file counter
fileCount = 1
startRow = 2 ' Start after header row

' Loop through the data and create new files
Do While startRow <= rowCount
endRow = startRow + maxRows - 1
If endRow > rowCount Then endRow = rowCount

' Create a new workbook
Set newWb = Workbooks.Add

' Copy header row
headerRow.Copy Destination:=newWb.Sheets(1).Rows(1)

' Copy the data rows
ws.Rows(startRow & ":" & endRow).Copy Destination:=newWb.Sheets(1).Rows(2)

' Save the new workbook
newWb.SaveAs Filename:=ThisWorkbook.Path & "\SplitFile_" & fileCount & ".xlsx"
newWb.Close SaveChanges:=False

' Increment counters
fileCount = fileCount + 1
startRow = endRow + 1
Loop

MsgBox "Files have been split successfully!"
End Sub