macro to transpose column data to row data and jump to next instance

[maggiesdirect]

I am trying to create a macro that will copy the data in columns AX1:HM1
and transpose starting at AW2. It would then jump to the next instance in AW where there is data and again copy the data in that row for AX??:HM?? to AW??.

I managed to record a macro that does everything but loop to the next instance. If someone can give me an idea how to keep it looping and copying the data in each subsequent row, that would be really helpful!

Thanks,
Bob

Here is a copy of the macro:
Sub transposing()
'
' transposing Macro
'
'

Selection.Copy
ActiveWindow.ScrollColumn = 199
ActiveWindow.ScrollColumn = 197
ActiveWindow.ScrollColumn = 196
ActiveWindow.ScrollColumn = 194
ActiveWindow.ScrollColumn = 192
ActiveWindow.ScrollColumn = 189
ActiveWindow.ScrollColumn = 187
ActiveWindow.ScrollColumn = 184
ActiveWindow.ScrollColumn = 181
ActiveWindow.ScrollColumn = 178
ActiveWindow.ScrollColumn = 175
ActiveWindow.ScrollColumn = 172
ActiveWindow.ScrollColumn = 169
ActiveWindow.ScrollColumn = 166
ActiveWindow.ScrollColumn = 163
ActiveWindow.ScrollColumn = 159
ActiveWindow.ScrollColumn = 155
ActiveWindow.ScrollColumn = 151
ActiveWindow.ScrollColumn = 148
ActiveWindow.ScrollColumn = 144
ActiveWindow.ScrollColumn = 140
ActiveWindow.ScrollColumn = 137
ActiveWindow.ScrollColumn = 133
ActiveWindow.ScrollColumn = 129
ActiveWindow.ScrollColumn = 125
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 89
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("AW3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=102
ActiveWindow.LargeScroll Down:=1
Range("AW139").Select
ActiveWindow.LargeScroll Down:=1
Range("AW175").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW139").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW103").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW67").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW31").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW31").Select
ActiveWindow.LargeScroll Down:=1
Range("AW67").Select
ActiveWindow.LargeScroll Down:=1
Range("AW103").Select
ActiveWindow.LargeScroll Down:=1
Range("AW139").Select
ActiveWindow.LargeScroll Down:=1
Range("AW175").Select
ActiveWindow.LargeScroll Down:=1
Range("AW211").Select
ActiveWindow.LargeScroll Down:=1
Range("AW247").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW211").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW175").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW139").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW103").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW67").Select
ActiveWindow.LargeScroll Down:=-1
Range("AW31").Select
End Sub
Sub transpose2()
'
' transpose2 Macro
'
' Keyboard Shortcut: Ctrl+Alt+T
'
Application.Goto Reference:="R2C50:R2C225"
Selection.Copy
Range("AW3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub

 

[venkat1926]

not very clearin delienating the requirement
column AX is 50th column
collumn HM is 221 column
that means there are 173 columns data
if this is transposed to AW2
then the data will run upto AW172 from AW2

do you want AX2:HM2 should be transposed to from AW 173??????


please claify

Besides recording macro to write a macro is an excellent exercise and learning process. But you must edit the macro while recording every keystroke genuine or mistake will be recorded. you must remove unnecesary statemnts. but for example series of ActiveWindow.ScrollColumnare not necessary. the best method in such cases you put a single apostrophe at the beginning of the statemnet which you require unnecessary and check if macro works you can even remove them. make a few experiments with small macros. You will learn the trick.