Related:
- Excel duplicate rows n times
- Excel duplicate row multiple times - Best answers
- Excel copy row multiple times - Best answers
- Copy and insert rows and number of times ✓ - Forum - Excel
- Excel duplicate row multiple times ✓ - Forum - Excel
- Copy row and insert n times ✓ - Forum - Office Software
- Macro to copy/paste down X times (where X is the number of rows) ✓ - Forum - Excel
- Repeat row x times - Forum - Excel
3 replies
Blocked Profile
So how is the data from A1:AY39 fit into A41?
Do you know how to build a "FOR..LOOP"?
Do you know how to build a "FOR..LOOP"?
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Hello Sarath,
If I've understood you correctly, you would like to have all the data from the range A1:AY39 copied/pasted onto the same sheet starting in cell A41 with this same data copied/pasted fifty times and with a blank row between each "block" of data. If I have assumed correctly, then the following simple code just may do the task for you:-
Test the code in a copy of your workbook first.
I hope that this helps.
Cheerio,
vcoolio.
If I've understood you correctly, you would like to have all the data from the range A1:AY39 copied/pasted onto the same sheet starting in cell A41 with this same data copied/pasted fifty times and with a blank row between each "block" of data. If I have assumed correctly, then the following simple code just may do the task for you:-
Sub CopySelection()
Dim DCT As Long '----> DCT simply stands for "Data Copy Times"
Dim i As Long
Application.ScreenUpdating = False
DCT = 50
For i = 1 To DCT
Range("A1:AY39").Copy Range("A" & Rows.Count).End(3)(2)(2)
Next i
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
Test the code in a copy of your workbook first.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Hello Sarath,
Yes. As follows:-
The second code(PBr) is called by the first code (line 13 of CopySelection).
The second code will place page breaks every 40 rows but it will not place vertical page breaks. As your data stretches out to Column AY, you may have to settle for the default setting for A4 size. You'll see this, and how the pages are numbered, once you run the code. You only have to run the CopySelection code as previous to see how it works.
This should give you a good start.
Again, test it in a copy of your workbook first.
Cheerio,
vcoolio.
Yes. As follows:-
Sub CopySelection()
Dim DCT As Long '----> DCT simply stands for "Data Copy Times"
Dim i As Long
Application.ScreenUpdating = False
DCT = 50
For i = 1 To DCT
Range("A1:AY39").Copy Range("A" & Rows.Count).End(3)(2)(2)
Next i
PBr
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
Sub PBr()
Dim lr As Long
ActiveSheet.ResetAllPageBreaks
lr = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = 41 To lr Step 40
ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Cells(i, 1)
Next i
End Sub
The second code(PBr) is called by the first code (line 13 of CopySelection).
The second code will place page breaks every 40 rows but it will not place vertical page breaks. As your data stretches out to Column AY, you may have to settle for the default setting for A4 size. You'll see this, and how the pages are numbered, once you run the code. You only have to run the CopySelection code as previous to see how it works.
This should give you a good start.
Again, test it in a copy of your workbook first.
Cheerio,
vcoolio.
Subscribe To Our Newsletter!
Thanks for Reply .
I have not said all the data of Row.(A1:AY39) should fit in single Rows A41 .
I asked i need to make copy of Row A1 : AY39 and should paste all those data With same cell Format in Next to it.(i.e on Origin as A41 ) and again after pasting the data from A41 it need to make another copy and it has to paste after 41 Rows.
This is the issue...
Thanks in Advance,
Sarath.