VBA Macro to Select range and copy paste to another sheet continuously

[Wizard23]

Hi,

I have a large data set over 200,000 rows but I would like to select a range i.e. A1-J2000 copy to another sheet then A1001-J2000 copy to another sheet? I was doing this manually but it will take forever does anyone have a simple code for my macro do this automatically without inputing each range that would be great !!

I'm also new to the forum so Hello to all of you!

Best

 

[DebugGalpin]

Are you copying a 1000 rows into each sheet? i.e you'll end up with over 200 separate sheets?
Do the sheets you plan to copy the data to already exist or are you creating them?
Are there any requirements around the copy and paste? i.e sheet 1 will have all the rows with a value of x or something like that?

 

[Wizard23]

Are you copying a 1000 rows into each sheet? i.e you'll end up with over 200 separate sheets?
Do the sheets you plan to copy the data to already exist or are you creating them?
Are there any requirements around the copy and paste? i.e sheet 1 will have all the rows with a value of x or something like that?

Hi,

The data is all on sheet 2 after being filtered.

No requirements, simply would like to slice the data from A1-J100 copy to sheet 3, slice data from A101-J200 copy to sheet 4 so there will be 2 sheets for instance but there is multiple data so when it's all said in done will be around 180 sheets total with all the data were copying.

Thanks for your response.

 

[DebugGalpin]

The code you are after I'll do now for you (just take a moment), however I think you really should probably think about exactly what you are trying to achieve as your end goal and whether having 180 sheets (untitled) of data with 1000 rows in each is really what you are after

 

[DebugGalpin]

Function CreateSheetIf(strSheetName As String) As Boolean
Dim wsTest As Worksheet
CreateSheetIf = False

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0

If wsTest Is Nothing Then
CreateSheetIf = True
Worksheets.Add.Name = strSheetName
End If

End Function

Sub CopyRanges()
Dim ws As String
s = 3
Start = 1
irow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Do Until Start > irow
ws = "Sheet" & s
If CreateSheetIf(ws) Then
End If

Sheets("Sheet2").Range(Cells(Start, 1), Cells(Start + 999, 10)).Copy Destination:=Sheets(ws).Cells(1, 1)

s = s + 1
Start = Start + 1000
Loop
End Sub

 

[Wizard23]

Function CreateSheetIf(strSheetName As String) As Boolean
Dim wsTest As Worksheet
CreateSheetIf = False

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0

If wsTest Is Nothing Then
CreateSheetIf = True
Worksheets.Add.Name = strSheetName
End If

End Function

Sub CopyRanges()
Dim ws As String
s = 3
Start = 1
irow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Do Until Start > irow
ws = "Sheet" & s
If CreateSheetIf(ws) Then
End If

Sheets("Sheet2").Range(Cells(Start, 1), Cells(Start + 999, 10)).Copy Destination:=Sheets(ws).Cells(1, 1)

s = s + 1
Start = Start + 1000
Loop
End Sub

Hey I'm trying to edit it but it's not working can you actually change it to copy range "A1-J3000" to sheet 3, copy range"A3001-J6000" to sheet 4 and so on until there is no more data? The data goes on for a while. I'm sorry about this confusion I thought I would be able to edit your code but I can't figure out where your change the cells selected.

I appreciate your help !

 

[DebugGalpin]

Yes you can change


Sheets("Sheet2").Range(Cells(Start, 1), Cells(Start + 999, 10)).Copy Destination:=Sheets(ws).Cells(1, 1)

and the

Start = Start + 1000

the 999 will become 2999
the 1000 will become 3000

 

[Trebor76]

Just out of interest - why do need a separate tab for each block of 1,000 records as it's rarely the most efficient way to do things especially considering the high number of tabs that will be created.

 

[Wizard23]

Yes you can change


Sheets("Sheet2").Range(Cells(Start, 1), Cells(Start + 999, 10)).Copy Destination:=Sheets(ws).Cells(1, 1)

and the

Start = Start + 1000

the 999 will become 2999
the 1000 will become 3000

Thank you so much was going to spend another 1-2 hours on this !! copy and pasting code lifesaver !

 

[Wizard23]

Just out of interest - why do need a separate tab for each block of 1,000 records as it's rarely the most efficient way to do things especially considering the high number of tabs that will be created.

Hi, its for a job I have no idea honestly. I was having trouble though with the macro. For some reason i'm getting a debug error does it work for you when you put in excel ? this is the line that needs to be debugged any help would be appreciated ! There might be more that needs to be debugged but do you see any errors in this code ?


Sheets("Sheet2").Range(Cells(Start, 1), Cells(Start + 999, 10)).Copy Destination:=Sheets(ws).Cells(1, 1)