VISHAL120
09-20-2011, 07:38 AM
Hi ALL,
first of all sorry for posting this on a new post as am not able to see my previous post i did on that.
I have been able to do transpose through lots of research from the net through this code of course with many modifications:
Sub Transposing_Cell_Route_data()
Dim x As Long
Dim lastrow As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("transpose_Route_type").Range("DATA_CLEAR").ClearContents
Sheets("Route_Type").Select
lastrow = Range("A65536").End(xlUp).row
For x = 5 To lastrow
Sheets("Route_Type").Select
Range("b" & x & ":N" & x).Copy
Sheets("transpose_Route_type").Range("B63536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
Sheets("Route_Type").Select
Range("O" & x & ":AA" & x).Copy
Sheets("transpose_Route_type").Range("c63536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
Sheets("Route_Type").Select
Range("AD" & x & ":AP" & x).Copy
Sheets("transpose_Route_type").Range("D63536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
Next x
Range("S5").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
But i have had to modify my data sheet by adding a control on that manually for seq. number.
Please see attached example.
I needed help on :
Can the code be more shorten and also is there a way to eliminate the Control for Transposing columns from my data sheet and also for the leadtime i have had to remove all formula and type manually as the transposing were not working well to do it without this.
many thanks for the kind help on that.
first of all sorry for posting this on a new post as am not able to see my previous post i did on that.
I have been able to do transpose through lots of research from the net through this code of course with many modifications:
Sub Transposing_Cell_Route_data()
Dim x As Long
Dim lastrow As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("transpose_Route_type").Range("DATA_CLEAR").ClearContents
Sheets("Route_Type").Select
lastrow = Range("A65536").End(xlUp).row
For x = 5 To lastrow
Sheets("Route_Type").Select
Range("b" & x & ":N" & x).Copy
Sheets("transpose_Route_type").Range("B63536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
Sheets("Route_Type").Select
Range("O" & x & ":AA" & x).Copy
Sheets("transpose_Route_type").Range("c63536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
Sheets("Route_Type").Select
Range("AD" & x & ":AP" & x).Copy
Sheets("transpose_Route_type").Range("D63536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
Next x
Range("S5").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
But i have had to modify my data sheet by adding a control on that manually for seq. number.
Please see attached example.
I needed help on :
Can the code be more shorten and also is there a way to eliminate the Control for Transposing columns from my data sheet and also for the leadtime i have had to remove all formula and type manually as the transposing were not working well to do it without this.
many thanks for the kind help on that.