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

Wizard23

New Member
Joined
Aug 19, 2015
Messages
10
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

Board Regular
Joined
Jun 29, 2011
Messages
175
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

New Member
Joined
Aug 19, 2015
Messages
10
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

Board Regular
Joined
Jun 29, 2011
Messages
175
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

Board Regular
Joined
Jun 29, 2011
Messages
175
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

New Member
Joined
Aug 19, 2015
Messages
10
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

Board Regular
Joined
Jun 29, 2011
Messages
175
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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.
 
Last edited:

Wizard23

New Member
Joined
Aug 19, 2015
Messages
10
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

New Member
Joined
Aug 19, 2015
Messages
10
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)
 

Forum statistics

Threads
1,081,545
Messages
5,359,450
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top