Macro / formula help please

[DebbyG]

I have a large series of data which I need to transpose from 2 x columns to rows.

This data is in blocks of 35 rows each.

Can someone tell me please how to write a Macro which will copy the next sequence of 35 rows (3 blank rows are skipped between each set of data) and transposer into a new spreadsheet.

I have tried copying a formula but cannot get it to accept a sequence of 35 rows.

many thanks
ray:

 

[DebbyG]

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

 

[Damon Ostrander]

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

 

[DebbyG]

Thank You

However I am having problems with the following

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

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

 

[Damon Ostrander]

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

 

[DebbyG]

Thank you Damon

I will give this a go.

I appreciate your reply.