Repeated copy/paste Macro

Closed
Sanju_7454 Posts 13 Registration date Thursday May 9, 2019 Status Member Last seen May 28, 2019 - Updated on May 16, 2019 at 11:53 AM
 Blocked Profile - May 9, 2019 at 12:06 PM
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.!

2 replies

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.

1
Sanju_7454 Posts 13 Registration date Thursday May 9, 2019 Status Member Last seen May 28, 2019 3
May 9, 2019 at 11:50 AM
Thank u very much.. I got the Idea. I will try and will post the result.
0
Sanju_7454 Posts 13 Registration date Thursday May 9, 2019 Status Member Last seen May 28, 2019 3
May 9, 2019 at 11:58 AM
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.
0
Blocked Profile
May 9, 2019 at 12:06 PM
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!
0