How to split rows of data into multiple sheets?

%3CLINGO-SUB%20id%3D%22lingo-sub-2544450%22%20slang%3D%22en-US%22%3EHow%20to%20split%20rows%20of%20data%20into%20multiple%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2544450%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20transfer%20my%20bookkeeping%20data%20from%20GoDaddy%20to%20QuickBooks.%20I%20have%20around%205300%20rows%20of%20data%20and%20QuickBooks%20only%20allows%201000%20rows%20per%20sheet.%26nbsp%3BI%20need%20to%20split%20my%20data%20into%206%20separate%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2544450%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2544622%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20rows%20of%20data%20into%20multiple%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2544622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1102640%22%20target%3D%22_blank%22%3E%40DoseofJose%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERun%20the%20following%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Test()%0A%20%20%20%20Dim%20ws%20As%20Worksheet%0A%20%20%20%20Dim%20wt%20As%20Worksheet%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20ws%20%3D%20ActiveSheet%0A%20%20%20%20m%20%3D%20ws.Range(%22A%22%20%26amp%3B%20ws.Rows.Count).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%201%20To%20m%20Step%201000%0A%20%20%20%20%20%20%20%20Set%20wt%20%3D%20Worksheets.Add(After%3A%3DWorksheets(Worksheets.Count))%0A%20%20%20%20%20%20%20%20ws.Range(%22A%22%20%26amp%3B%20r).Resize(1000).EntireRow.Copy%20Destination%3A%3Dwt.Range(%22A1%22)%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm trying to transfer my bookkeeping data from GoDaddy to QuickBooks. I have around 5300 rows of data and QuickBooks only allows 1000 rows per sheet. I need to split my data into 6 separate sheets.

1 Reply

@DoseofJose 

Run the following macro:

Sub Test()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    m = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    For r = 1 To m Step 1000
        Set wt = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        ws.Range("A" & r).Resize(1000).EntireRow.Copy Destination:=wt.Range("A1")
    Next r
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub