Repeated copy/paste Macro

Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
-
I have basic knowledge of Macros.

I need to copy 1Lakh data in such a way that it takes 10k rows and paste in new sheet1 and again it takes next 10k and paste in new sheet2.

Thanks for ur time.!
See more 

2 replies

Best answer
Posts
12350
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
June 14, 2019
1056
1
Thank you
So record an macro that select the first X number of rows. Copy them. Select new sheet. Paste them. Stop recording thw macro. Now go into that macro and change the range to whatever number you wish. then cut and paste that macro and put in as many line cut and paste that you wish! Utlizong variables instead of static numbers. Post back afyer you have that, if you have any questions. It is that simple.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3794 users have said thank you to us this month

Sanju_7454
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3 -
Thank u very much.. I got the Idea. I will try and will post the result.
Respond to ac3mark
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3
0
Thank you
Sub CopyPasteSubsequentRange()

'
Range("A1:A3").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A4").Select


Range("A4:A8").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A9").Select

Range("A9:A12").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A12").Select

End Sub


Thanks a lot @ac3mark for your time and valuable guidence.
ac3mark
Posts
12350
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
June 14, 2019
1056 -
Ok, now all you have to do is set a variable for each start and end range. So you go like:
Startrange1="A1"
Endrange1="a1000"

Startrange2=startrange1+1000
Endrange2=endrange1+1000

Then replace the code with:
Range (startrange1 &"-"& endrange1).select

And so on. Post back if you have any other questions. Thank you so much for taking the time to learn this very simple solution for simple macros!
Respond to Sanju_7454