Transposing using VBA

[ongcy]

Hello,

I have a problem with transposing a row of Data into a column in another worksheet using code. Can anyone help? Below is the code i have used and the problem part is in italics, any pointers will be greatly appreciated. Thanks!


Kelvin

Sub CopyData()

Dim ColData As Range
Dim RowData As Range

With ColData

Worksheets("Raw Data").Range("B8:B108").Copy _
Destination:=Worksheets("Selected Data").Range("A1")

Worksheets("Raw Data").Range("D8:D108").Copy _
Destination:=Worksheets("Selected Data").Range("B1")
End With

With RowData

Worksheets("Raw Data").Range("A121:AI121").Copy _
Destination:=Worksheets("Selected Data").Range("D1:D300")


Worksheets("Raw Data").Range("A122:AI122").Copy _
Destination:=Worksheets("Selected Data").Range("E1:E300")

End With

End Sub

 

[cmefly]

Hello,

I have a problem with transposing a row of Data into a column in another worksheet using code. Can anyone help? Below is the code i have used and the problem part is in italics, any pointers will be greatly appreciated. Thanks!


Kelvin

Sub CopyData()

Dim ColData As Range
Dim RowData As Range

With ColData

Worksheets("Raw Data").Range("B8:B108").Copy _
Destination:=Worksheets("Selected Data").Range("A1")

Worksheets("Raw Data").Range("D8:D108").Copy _
Destination:=Worksheets("Selected Data").Range("B1")
End With

With RowData

Worksheets("Raw Data").Range("A121:AI121").Copy _
Destination:=Worksheets("Selected Data").Range("D1:D300")


Worksheets("Raw Data").Range("A122:AI122").Copy _
Destination:=Worksheets("Selected Data").Range("E1:E300")

End With

End Sub

hey kelvin,

try something like this:
sub transpose ()
Range("A122:AI122").Select
Selection.Copy
Sheets("destination").Select
Range("E1:E300").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
end sub

 

[ongcy]

Hello Cmefly,

Thank you for repsonding so quickly, I tried the code u gave me but wat i got was the msg "Runtime error 1004" Application-defined or object-defined error. I tried to copy excatly but got the same error, so i modified a little and still got the same error. Did i do anything incorrectly?


Regards,
Kelvin

Sub transpose()

Worksheets("Raw Data").Range("A122:AI122").Select
Selection.Copy
Worksheets("Selected Data").Select
Range("E1:E300").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, transpose:=True
Application.CutCopyMode = False

End Sub

 

[Tom Urtis]

One of the reasons that jumps out is that in your italicized code is attempting to copy 100 cells into a destination range of 300 cells. You seem to move away from what's working with the first two code blocks by simply referencing the first (upper left corner) cell of the destination ranges. Try being consistent with your destination range syntax, and certainly, don't expect error free code when your source copy range has a different quantity of specified cells than the destination range's specified quantity of cells. Also, keep in mind that there are only 256 columns in Excel, so transposing more than 256 source row cells is not possible.

 

[ongcy]

Hello Tom,

I've tried to limit the target cells to excatly the cells i am copying, but i still face the same problem error msg, "Runtime error 1004" Any ideas how where i've gone wrong with the code in Bold italics?


Regards,
Kelvin

Sub TransferData()

Dim ColData As Range
Dim RowData As Range

With ColData

Worksheets("Raw Data").Range("B8:B108").Copy _
Destination:=Worksheets("Selected Data").Range("A1")

Worksheets("Raw Data").Range("D8:D108").Copy _
Destination:=Worksheets("Selected Data").Range("B1")
End With

With RowData

Worksheets("Raw Data").Range("A124:AI124").Copy _
Destination:=Worksheets("Selected Data").Range("D1:D35").PasteSpecial, Paste:=xlAll, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, transpose:=True


End With
End Sub

 

[chiello]

Ciao ongcy,
try the following:

Worksheets("Raw Data").Range("A124:AI124").Copy
Worksheets("Selected Data").Range("D1:D35").PasteSpecial, Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

best regards

 

[ongcy]

Ciao Chiello,

Grazie, i tried and it shows me and it shows me the error msg "Runtime Error 450" Wrong number of arguements or invalid Property assginments. Any advice to counter this? The below code is the one u showed me, hope i copied it correctly.


Rgds
Kelvin

With RowData

Worksheets("Raw Data").Range("A124:AI124").Copy
Worksheets("Selected Data").Range("D1:D36").PasteSpecial , Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End With

 

[Tom Urtis]

Hello Tom,
I've tried to limit the target cells to excatly the cells i am copying, but i still face the same problem error msg, "Runtime error 1004" Any ideas how where i've gone wrong with the code in Bold italics?With RowData

Worksheets("Raw Data").Range("A124:AI124").Copy _
Destination:=Worksheets("Selected Data").Range("D1:D35").PasteSpecial, Paste:=xlAll, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, transpose:=True


End With
End Sub

Do you have merged cells in any of those ranges?


Try running this code line in a separate macro:

Sub Test1()
Worksheets("Raw Data").Range("A124:AI124").Copy Worksheets("Selected Data").Range("D1")
End Sub


What happens when you try that?

 

[chiello]

Copy the following. I'm nearly sure it works

Worksheets(1).Range("A124:AI124").Copy
Worksheets(2).Range("D1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Ciao.. and let me know

 

[ongcy]

Hello,

The code that u guys pasted are correct. I've done it wrongly but putting the range wrongly! That was dumb of me, sorry for the trouble guys! Grazie Cheillo, and Thanks Tom!