Macro to section out data from master sheet

[lyn5339]

i am working on a code to copy data from a loarge amount of data on a master worksheet and copy every 18,000 rows and place it on a new worksheet and then retrieve the next 18,000 from the master sheet and place it on a new sheet. i had the code to create the worksheet but am having trouble actually getting it loop and grab the next 18,000 without overlapping and copying the same data. anything ideas? let me know if anyone wants more clarification on the project.

 

[nuked]

Can you post your code, and why are you doing it in batches?

 

[lyn5339]

i created a macro that can only run through 18,000 rows at a time. i troubleshooted this before and think it is because the macro involves inserting rows, Excel can only insert so many rows in one take. as for code all i have is the generic code to add the a work sheet and name it. i can record a mcro to get the code to cut from one sheet to another but i do not know where to start as far as taking the 18,000 chunks and then returning for the next chunk.

 

[nuked]

Really gonna need either your code so far, or a more detailed spec. Otherwise I'm just going to give vague guidance which will likely not result in a solution for you.

 

[njimack]

As nuked says, you won't get a customised solution without providing a lot more detail.

Below is some generic code you may be able to use. It will create a new worksheet for each batch of 18,000 rows...

Sub Copy_18k()
Dim Last_Row As Long
Dim i As Long
Dim Limit As Long
Application.ScreenUpdating = False
Limit = 18000
With Sheets("Master Worksheet")
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To WorksheetFunction.Ceiling(Last_Row / Limit, 1)
        Range(.Cells(((i - 1) * Limit) + 1, 1), .Cells((i + 1) * Limit, 10)).Copy
        Sheets.Add
        ActiveSheet.Paste
    
    Next i
    
End With
Application.ScreenUpdating = True
End Sub

 

[lyn5339]

this is all i had so far which i got off micrsoft's support site, i have only started learning macros and VBA for a little over a week:
Sub AddSheets()

Set newSheet = Worksheets.Add
newSheet.Name = "Data Sheet" + sheets.count