Copy rows n times in Excel

Solved/Closed

Sarath - Updated on Dec 16, 2018 at 06:40 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Dec 20, 2018 at 05:27 AM

Hello,

I Need to copy from rows from A1:AY39 and need to insert in A41 and the same as be pasted on next 38 Row from A41 and it as to be done for 50 Times

Kindly Provide Solution ASAP

Excel duplicate rows n times
Copy row and insert n times ✓ - Office Software Forum
You have guessed too many times ✓ - WhatsApp Forum
How to duplicate a google doc - Guide
Excel date format dd.mm.yyyy - Guide
You've tried to log in too many times. to protect your account, a temporary block has been placed on logging in. please try again later. ✓ - Facebook Forum

3 responses

Answer 1 / 3

Blocked Profile
Dec 14, 2018 at 05:42 PM

So how is the data from A1:AY39 fit into A41?

Do you know how to build a "FOR..LOOP"?

Sarath
Dec 14, 2018 at 11:04 PM

Hi Ac3Mark,

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.

Answer 2 / 3

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Dec 15, 2018 at 11:55 PM

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:-

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.

Blocked Profile
Dec 17, 2018 at 04:52 PM

Hopefully they can construct a FOR LOOP now!

Sarath
Dec 18, 2018 at 02:14 AM

Hi vcoolio,

Now it works fine...

Sarath
Dec 20, 2018 at 01:19 AM

Hi Vcoolio,

Is there possibilities to make page break during Inserting those data.

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Dec 20, 2018 at 05:27 AM

Hello Sarath,

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.

Similar discussions

How to duplicate rows x number of times

Copy and insert rows and number of times

Copy row and insert n times

Repeat row x times

Excel VBA copy and paste loop with logic condition

Excel - Repeat rows a specified number of times

Similar discussions

Subscribe To Our Newsletter!