Macro / formula help please

Re: Macro / formula range help please

I have figured out the below, but I have to freetype the Range to paste from and the Range to paste to (i.e. B534:B571) & (A17)

Can anyone let me have a formula to increase each range by 37 rows and paste into the next blank row.

I need to go up to range B19192:B19229 - hence needing your help please.

Many thanks


ray:

Range("B534:B571").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("A17").Select
ActiveWindow.ActivateNext


Range("B572:B609").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("A18").Select
ActiveWindow.ActivateNext

Range("B610:B647").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("A19").Select
ActiveWindow.ActivateNext

Hi DebbyG,

Is this what you are looking for?


Dim iRow As Long
Dim ToRow As Long

ToRow = 17

For iRow = 534 To 19192 Step 38

Range(Cells(iRow,2),Cells(iRow+37,2).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Cells(ToRow,1).Select
ActiveWindow.ActivateNext

ToRow = ToRow + 1

Next iRow



Damon

Thank You

However I am having problems with the following

Range(Cells(iRow,2),Cells(iRow+37,2).Select

Click to expand...

I have insert another ")" after (iRow,2) but I am getting an error message saying

Compile Error - Invalid use of Property.

Can anyone help please.

Many thanks

Hi again DebbyG,

Yes, I goofed and forgot the closing right parenthesis. But it needs to be at the end of the Range statement, like this:

Range(Cells(iRow,2),Cells(iRow+37,2)).Select

Damon

Thank you Damon

I will give this a go.

I appreciate your reply.