Select Range every 20 rows copy & paste special

[Guest]

Hi -

I am trying to build a macro that will select the same sized range G45:T48 then copy, paste special then go down 20 rows and do the same thing. Below is some code I started. I am not good with the Do While or Do Until type functions. Can someone point me in the right direction. I need this to go down to row 2000.

Range("G45:T48").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("G65:T68").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Thanks, Adam

 

[DavidC]

Use the activecell.offset(r,c) function. where r is the
number of rows below the active cell to drop down, and c
is the number of columns along to move. If the columns
are always the same then use 0 for c.

Another way is to use the cells reference to select the
range, so:

Loop until (whatever the criteria is for running this
routine)[An alternative is to do a for a=1 to (however
many times you need this to run)]
With ActiveSheet
.Range(.Cells(row1, 1), .Cells(rownum + 1, 39)).Select
End With
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

row1=row1+20

rownum=rownum+20

end loop [alternative next a]

Best of Luck

DavidC

-----Original Message-----
Hi -

I am trying to build a macro that will select the same

sized range G45:T48 then copy, paste special then go down
20 rows and do the same thing. Below is some code I
started. I am not good with the Do While or Do Until type
functions. Can someone point me in the right direction.
I need this to go down to row 2000.

Range("G45:T48").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,

Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("G65:T68").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,

Operation:=xlNone, SkipBlanks _