Split data into multiple worksheets.

I'm trying to split a big list of data into multiple worksheets by row count of every 44 of data. Also, I want to copy the first row (heading) to every new worksheet.

The problem is that I can't understand what is wrong with the code. It process the first 44 rows, stops and shows "Run Time Error 1004".
When I click debug, it shows this row: --> Range(.Range("A1:C1").Address(0, 0) & "," & .Range(Cells(i, "A"), Cells(i, "C").Resize(44)).Address(0, 0)).Copy


Sub test()
Dim iCalc As Long, i As Long

iCalc = Application.Calculation
With Application
.Calculation = xlManual
.ScreenUpdating = False
.EnableEvents = False
End With

With ThisWorkbook.ActiveSheet
For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Step 44
Range(.Range("A1:C1").Address(0, 0) & "," & .Range(Cells(i, "A"), Cells(i, "C").Resize(44)).Address(0, 0)).Copy
Worksheets.Add
Range("A1").PasteSpecial xlPasteValues
Next i
End With
With Application
.Calculation = iCalc
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Try this macro. Change the sheet name (in red) to suit your needs.

Please Login or Register  to view this content.

Thanks a lot! It worked!!!

You are very welcome.