Automated transposing

[ndroger1]

I am having trouble getting my transposing macro to be either automated, or at the very least more efficient than running it 585 times and typing in the cell range. I need to copy the first 29 rows of columns A-C and then transpose and paste every 3rd row down in colomn H or I. The ouput I am trying to achieve is the image below. Currently my macro does this one range at a time, which takes forever as I have 585 per worksheet and 5 worksheets to do this in.

Sub TransposeColumnsRows()
Dim SourceRange As Range
Dim DestRange As Range

Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)

SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

End Sub


This is the working macro I currently have, any help is appreciated

 

[My Aswer Is This]

You said:

I need to copy the first 29 rows but your image seems to indicate stating in row 2
And you said:

which takes forever as I have 585 per worksheet

If your only doing row 2 to row 29 how would this end up being 585 per sheet?​

​

 

[ndroger1]

You said:
I need to copy the first 29 rows but your image seems to indicate stating in row 2
And you said:
which takes forever as I have 585 per worksheet



If your only doing row 2 to row 29 how would this end up being 585 per sheet?

My data is currently stacked in such a way that every 29x3 cell block is a single observation and there are 585 observations per workbook. The image is just a very small snapshot of my entire set and was to demonstrate what I need my transpose to look like.

 

[My Aswer Is This]

Your original post said:

have 585 per worksheet and 5 worksheets to do this in.
Your last posting said:

585 observations per workbook

So are we dealing with different worksheets or different Workbooks.

So hopefully someone else here on the forum will be able to help you.​

​

 

[ndroger1]

Your original post said:
have 585 per worksheet and 5 worksheets to do this in.
Your last posting said:
585 observations per workbook



So are we dealing with different worksheets or different Workbooks.



So hopefully someone else here on the forum will be able to help you.

They are different workbooks but I just need the macro to work and be automated on one of them, as I will use the same macro on each workbook

 

[Fluff]

Are all the 585 observations in cols A:C of one sheet?

 

[ndroger1]

Are all the 585 observations in cols A:C of one sheet?

Yes

 

[Fluff]

In that case try

Sub ndroger()
   Dim i As Long
   
   For i = 2 To Range("A" & Rows.Count).End(xlUp).row Step 29
      Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(3, 29).Value = Application.Transpose(Range("A" & i).Resize(29, 3))
   Next i
End Sub

 

[ndroger1]

In that case try

Sub ndroger()
   Dim i As Long
  
   For i = 2 To Range("A" & Rows.Count).End(xlUp).row Step 29
      Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(3, 29).Value = Application.Transpose(Range("A" & i).Resize(29, 3))
   Next i
End Sub

That seems to have worked wonderfully. Thank you, thank you

 

[Fluff]

You're welcome & thanks for the feedback