Macro copy paste

[Jeff12]

Hi

I have a grid of data from Y8 to AV18. I have a macro that scrolls through a list which will change the data as it goes through the list.

I have 70 different grids and I want the first one to be copied and pasted a8 to w18, the second to a20 to w30, third a32 to w42.

In the code below the range "MacroCorner" is cell a7 and "MacroCopy" is the grid of data from Y8 to AV18. I just need som helping landinf the copied grids in the correct place. Do I use Cells (,) ?


thanks in advance

Jeff

For x = 1 To 70


Sheets("Output One Page Summary").Select
Range("B2") = x
Calculate


Range("MacroCopy").Copy
Range("MacroCorner").Cells(?,?).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Next x

 

[antialias401]

Try this:

If you select the entire range to copy, you only need 1 cell to begin pasting.

Sub Test_Copy()

    Range("MacroCopy").Copy Destination:=Range("MacroCorner")
    
End Sub

This assumes that "MacroCopy" is actually: Range("Y8:AV18")
And that "MacroCorner" is one cell: Range("A8")
If not, then test it this way instead.

Sub Test_Copy()

   'Make Sure these Ranges Are Correct
   Range("Y8:AV18").Copy Destination:=Range("A8")
   Range("Y20:AV28").Copy Destination:=Range("A20")
   Range("Y32:AV40").Copy Destination:=Range("A32")

    
End Sub

That will copy Y8:AV18 and paste it into A8:W18
You can do the same for the rest of your "grids".
Once you copy a range, you can select one cell to start the paste range, so you don't really need: .Cells(?,?).Select

Hope this sends you in the right direction.

 

[Jeff12]

Try this:

If you select the entire range to copy, you only need 1 cell to begin pasting.

Sub Test_Copy()

    Range("MacroCopy").Copy Destination:=Range("MacroCorner")
    
End Sub

This assumes that "MacroCopy" is actually: Range("Y8:AV18")
And that "MacroCorner" is one cell: Range("A8")
If not, then test it this way instead.

Sub Test_Copy()

   'Make Sure these Ranges Are Correct
   Range("Y8:AV18").Copy Destination:=Range("A8")
   Range("Y20:AV28").Copy Destination:=Range("A20")
   Range("Y32:AV40").Copy Destination:=Range("A32")

    
End Sub

That will copy Y8:AV18 and paste it into A8:W18
You can do the same for the rest of your "grids".
Once you copy a range, you can select one cell to start the paste range, so you don't really need: .Cells(?,?).Select

Hope this sends you in the right direction.

Thanks but i forgot to say the range I need to copy is always Y8:AV18 (this is where the formulas are) and i need to start it in a8 then the next one will start in a20 then the next in a32 etc until its gone through 70 times. I'm just not sure how to copy and paster the blocks down??

 

[antialias401]

Ah, I was wondering what your loop was for!

Try this instead:

Sub Test_Copy()

    Sheets("Output One Page Summary").Select

    'Copy/Paste The First Set
    Range("Y8:AV18").Copy 
    Range("A8").PasteSpecial xlPasteValues
    
    'Copy Same Data 69 More Times, Into Every 12th Row
    For i = 1 To 69
        x = x + 12
        Range("Y8:AV18").Copy Destination:=Range("A" & x + 8)
    Next i
   

End Sub

Edit: If you want to Paste Values Only:

Sub Test_Copy()

    Sheets("Output One Page Summary").Select

    'Copy/Paste The First Set
    Range("Y8:AV18").Copy Destination:=Range("A8")
    
    'Copy Same Data 69 More Times, Into Every 12th Row
    For i = 1 To 69
        x = x + 12
        Range("Y8:AV18").Copy
        Range("A" & x + 8).PasteSpecial xlPasteValues
    Next i
   

End Sub

 

[antialias401]

Here is a small fix to the paste values code:
The previous code did not paste values in the very 1st set...

Sub Test_Copy()

    Sheets("Output One Page Summary").Select

    'Copy/Paste The First Set
    Range("Y8:AV18").Copy
    Range("A8").PasteSpecial xlPasteValues
    
    'Copy Same Data 69 More Times, Into Every 12th Row
    For i = 1 To 69
        x = x + 12
        Range("Y8:AV18").Copy
        Range("A" & x + 8).PasteSpecial xlPasteValues
    Next i
   
End Sub

This will start it in A8 then duplicate A20, A32, etc, etc 70 times.