Macro to Paste Selection Every 12 Rows

[garygraham]

I'm new to macros but trying to learn. Thanks in advance for your help.

I'm trying to copy a range of cells A3:G3 (with formulas) and then paste it every 12 rows to the end of the data which is row 67,789. In other words the range A3:G3 would be pasted into A15, A27, A39, A51, etc. all the way to A67,789.

Thanks,
Gary

 

[garygraham]

That created a spreadsheet with > 1M rows.

Maybe I'm not going about this in the best way. Let me explain what I'm trying to accomplish and you tell me what the best method is.

https://picasaweb.google.com/lh/photo/O6Mh3NUgpWHuec1IwaRHjw?feat=directlink

The red rows (Jan Data) already exists for each customer.
The yellow rows simply reference the row above for customer info for data months Feb - Dec. I want to copy those formula from the yellow rows to the green sections below such that the respective red rows in each section are copied to the green sections. Have I totally confused you yet???

Thanks,
Gary

 

[Blade Hunter]

Right I get it, you want to copy 3:13 to 15:25 and alll the way down leaving out the red rows yes?

 

[garygraham]

Yes but it has to paste the formulas such that they pick up the data from the red rows in each respective section.

Thanks,
Gary

 

[Blade Hunter]

And how far down do you want it to paste?

 

[garygraham]

The last section to be pasted into is A67587:G67597.

Gary

 

[Trebor76]

Hi Gary,

Here's my attempt, which like Balde Hunter said will take a while to run:

Sub Macro2()

    Dim lngLastRow As Long, _
        lngPasteRow As Long
    
    lngLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Application.ScreenUpdating = False
    
    For lngPasteRow = 2 To lngLastRow Step 12
        If lngPasteRow > 2 Then
            Range("A2:G2").Copy Destination:=Range("A" & lngPasteRow & ":G" & lngPasteRow)
        End If
    Next lngPasteRow
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert

 

[Blade Hunter]

Sorry, got stuck in a meeting, looks like Robert sorted it for you

 

[garygraham]

I built a very basic but very long macro that I had to break into multiple parts that I think got the job done.

Thanks guys, I'll give your much better solution a shot tomorrow.
It's after 2:00 Am here and I'm going to bed.

Thanks again for all the help.
Gary

 

[garygraham]

Trebor76,

I tried your macro. It completed in less than 1 minute but I don't see where it actually did anything. Did I do something wrong?

A link to the file in Goggle Docs is attached.

https://docs.google.com/leaf?id=0B9...DUtZDI2MTIxODNlYjNi&hl=en_US&authkey=CKWyofgL

Thanks,
Gary

 

[Trebor76]

Hi Gary,

It's done the job - i.e. copied the range A2:G2 to every 12th row.

Do you need the range to be A2:G13?

Robert