1

Thanks for taking the time to read my post. I am attempting to write my first VBA macro and am getting myself confused about what to do next. I found a few pieces of code online that I cobbled together, which work perfectly on the first go around of the loop but don't perform the expected task in subsequent loops.

My goal is to copy and transpose data from a single column several thousand cells tall into a new location 22 cells at a time. So my loop is meant to copy H2:H23, then move down and copy H24:H46, and so on. Each time it transposes and pastes the data into a new location one cell lower than the most recent pasting. When it runs the way it is now I find that the code makes a single copy and I see it selecting the next range down but I can't figure out how to make it remember that range and continue to transpose the rest of the row.

If you can figure out how to make this work I would be forever grateful!

Sub TranspositionLoop()

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)

For x = 1 To 257

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

SourceRange.Offset(23, 0).Select
DestRange.Offset(1, 0).Select

Next x

End Sub
Mikku
  • 6,538
  • 3
  • 15
  • 38
Adornsy
  • 11
  • 4
  • 3
    Does https://stackoverflow.com/a/21129568/11683 solve your problem? – GSerg Jan 04 '19 at 18:37
  • 2
    You really want to [avoid the use of Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. it will not always result in the cell you expect being selected. – cybernetic.nomad Jan 04 '19 at 18:37

1 Answers1

0

I figured it out.

Sub TranspositionLoop()

Dim SourceRange As Range
Dim DestRange As Range
Dim x As Integer

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)

For x = 1 To 5

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

Set SourceRange = SourceRange.Offset(22, 0)
Set DestRange = DestRange.Offset(1, 0)

Next x

End Sub
Adornsy
  • 11
  • 4
  • @Adomsy: Nice. Try [Transpose Column to Range](https://www.dropbox.com/s/bdyt70cg9zm74rz/vba-transposing-loop-source-issue_54044300.xls?dl=0). – VBasic2008 Jan 04 '19 at 20:41