"next" function not working

[xxgirlinbluexx]

I wrote a macro to split a file into rows of 20 and save them each in separate workbooks. It worked the first time round. However, I'm trying the macro a few days after and it no longer works! I only get 1 file with the first 20 rows and the macro stops there. There appear to be no bugs at all. Any way I can fix the problem? I have copied the code here for reference:

Sub SplitSheet()
'Split data lines into rows of n lines each and save into another workbook
 
Dim lastRow As Long, myRow As Long, myBook As Workbook
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 1 To lastRow Step 20
    Set myBook = Workbooks.Add
    ThisWorkbook.Sheets("Sheet1").Rows(myRow & ":" & myRow + 19).EntireRow.Copy myBook.Sheets("Sheet1").Range("A1")
With ActiveWorkbook
                .SaveAs Filename:="C:/File" & myRow & ".xls"
                .Close
            End With
 
 
Next myRow
End Sub

Any help is appreciated!

 

[xxgirlinbluexx]

Just to add on, the original document that I am splitting up has 17000 rows and 7 columns of data

I wrote a macro to split a file into rows of 20 and save them each in separate workbooks. It worked the first time round. However, I'm trying the macro a few days after and it no longer works! I only get 1 file with the first 20 rows and the macro stops there. There appear to be no bugs at all. Any way I can fix the problem? I have copied the code here for reference:

Sub SplitSheet()
'Split data lines into rows of n lines each and save into another workbook
 
Dim lastRow As Long, myRow As Long, myBook As Workbook
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 1 To lastRow Step 20
    Set myBook = Workbooks.Add
    ThisWorkbook.Sheets("Sheet1").Rows(myRow & ":" & myRow + 19).EntireRow.Copy myBook.Sheets("Sheet1").Range("A1")
With ActiveWorkbook
                .SaveAs Filename:="C:/File" & myRow & ".xls"
                .Close
            End With
 
 
Next myRow
End Sub

Any help is appreciated!

 

[Norie]

Does this work?

Sub SplitSheet()
Dim lastRow As Long, myRow As Long, myBook As Workbook
    
    'Split data lines into rows of n lines each and save into another workbook

    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For myRow = 1 To lastRow Step 20
    
        Set myBook = Workbooks.Add
        
        ThisWorkbook.Sheets("Sheet1").Rows(myRow & ":" & myRow + 19).EntireRow.Copy myBook.Sheets("Sheet1").Range("A1")
        
        With myBook
            .SaveAs Filename:="C:/File" & myRow & ".xls"
            .Close
        End With
        
    Next myRow
    
End Sub

 

[Jonmo1]

What is the value of LastRow at the time the loop starts ?
You can open the Locals Window in the VBA window, and step through the code with VBA.

My guess would be that the LastRow is <20

It could be the use of ThisWorkbook, that refers to the Book that contains the code.
Maybe you could try ActiveWorkbook instead.
Or better yet, fully specify the correct book, Workbooks("bookname.xlsx")

 

[xxgirlinbluexx]

How about this? I named the original workbook "Workbook2.xlsm" where the last row of data is at 17387. However, now the code doesnt even generate a file. Am I missing something?

Sub SplitSheet()
'Split data lines into rows of n lines each and save into another workbook


Dim lastRow As Long, myRow As Long, myBook As Workbook
For myRow = 1 To lastRow = 17387 Step 20
    Set myBook = Workbooks.Add
    Workbooks("Workbook2.xlsm").Sheets("Sheet1").Rows(myRow & ":" & myRow + 19).EntireRow.Copy myBook.Sheets("Sheet1").Range("A1")
With ActiveWorkbook
                .SaveAs Filename:="C:/File" & myRow & ".xls"
                .Close
            End With




Next myRow
End Sub

What is the value of LastRow at the time the loop starts ?
You can open the Locals Window in the VBA window, and step through the code with VBA.

My guess would be that the LastRow is <20

It could be the use of ThisWorkbook, that refers to the Book that contains the code.
Maybe you could try ActiveWorkbook instead.
Or better yet, fully specify the correct book, Workbooks("bookname.xlsx")

 

[Jonmo1]

Now you haven't given a value to the LastRow Variable.