VBA: Copy one cell and paste every other row until the end

[NikoleJay]

Hi!

I am creating a product that reorganizes a report. I have figured out most of what I want to do, but have not found an example of a code I know how to edit to my needs for one of the commands.

I need to be able to copy row 5 (which is merged from columns A through J with G and H hidden) and paste that into every other row. This will be a report that is run weekly and will have a changing number of rows, so I also need it to stop pasting after the last row with data.

Basically, I need to turn this:

Doc Number	Transaction Type	#Operation Date	Operation No	NSN	Noun	Quantity	Created By
xxxxxxxxx	xxxxxxxxx	5/7/2020 19:00	xxxxxxxxxx	xxxxxxxxx	xxxxxxxxxxx	33EA	xxxxxxxxxx
Remarks:_______________________
xxxxxxxxx	xxxxxxxxx	5/7/2020 19:00	xxxxxxxxxx	xxxxxxxxx	xxxxxxxxxxx	33EA	xxxxxxxxxx
xxxxxxxxx	xxxxxxxxx	5/7/2020 19:00	xxxxxxxxxx	xxxxxxxxx	xxxxxxxxxxx	33EA	xxxxxxxxxx

Into this (with merged cells):

Doc Number	Transaction Type	#Operation Date	Operation No	NSN	Noun	Quantity	Created By
XXXXXXXXXXXX	XXXXXXXXXXXX	5/5/2020 19:41	XXXX	XXXXX	XXXXXXXX	1EA	XXXXXXXX
Remarks:_______________________
XXXXXXXXXXXX	XXXXXXXXXXXX	5/5/2020 19:41	XXXX	XXXXX	XXXXXXXX	1EA	XXXXXXXX
Remarks:_______________________
XXXXXXXXXXXX	XXXXXXXXXXXX	5/5/2020 19:41	XXXX	XXXXX	XXXXXXXX	1EA	XXXXXXXX
Remarks:_______________________

I attached pictures as well. Any help will be greatly appreciated!!

 

[Joe4]

Welcome to the Board!

Try this:

Sub MyCopyMacro()

    Dim r As Long

    Application.ScreenUpdating = False
    
'   Set initial r value
    r = 6
    
'   Do Loop
    Do
'       Copy row
        If Cells(r, "A") <> "" Then
            Rows(5).Copy Cells(r + 1, "A")
'       Else exit loop
        Else
            Exit Do
        End If
'       Increment row by 2
        r = r + 2
    Loop
        
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[NikoleJay]

It worked!! Thank you so much!!

 

[Joe4]

You are welcome.
Glad I was able to help!