Splitting one worksheet into multiple sheets within the same workbook based on row number

Hi All,

I have been trying to do a VBA code to split one excel sheet into multiple sheets within a workbook. Lets say, I have a sheet with data on 100 different parameters. I would like to seperate that excel sheet into multiple sheets - one sheet for each parameter. The sheet is to be split after every 70th row. I tried the below code (I'm not a expert, just have basic understanding of VBA coding). However, it is always throwing a type mismatch error or some othe error.

Sub test()

Dim lastRow As Long, myRow As Long, myBook As Sheets
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 2 To lastRow Step 69 ----------------------------------------------------"Every 70th row the sheet is to be split and copied to a new sheet"

Set myBook = ThisWorkbook.Sheets.Add ----------------------" Adding new sheet in the same workbook"

ThisWorkbook.Sheets("Sheet1").Rows(myRow & ":" & myRow + 69).EntireRow.Copy myBook("A1") -------------- "copying to new sheet"

Next myRow

End Sub


Could anyone help me with this.

Thanks a lot. It worked!