Please help with Paste Special VBA

[hunter9002]

I am trying to write a code where I select the active cell's row on sheet1, copy the whole row, and then paste the information vertically starting at cell B5 on sheet3.

So far I have been able to select and copy the relevant row, but I am getting errors when I paste.

Here is what I have thus far:

Private Sub CommandButton1_Click()

Dim rngData As Range

Set rngData = Rows(ActiveCell.Row)
rngData.Copy ("Sheet3")

Range("B5").Select
rngData.PasteSpecial xlPasteValues: Transpose = True

Application.CutCopyMode = False
Me.UsedRange.Rows.AutoFit
End Sub

 

[gsistek]

try replacing
rngData.PasteSpecial xlPasteValues: Transpose = True

with
rngData.PasteSpecial Paste:=xlPasteValues, Transpose:=True

 

[hunter9002]

That probably works, but didn't fix it. The problem must be elsewhere.I'm getting an application-defined or objected-defined error.

 

[gsistek]

Try using this:

Private Sub CommandButton1_Click()

Dim rngData As Range

Set rngData = ActiveCell.Row
rngData.Copy

Sheets("Sheet3").Range("B5").PasteSpecial _
xlPasteValues: Transpose = True

Application.CutCopyMode = False

End Sub

 

[hunter9002]

Thanks for sticking by.

Now I am getting an subscript out of range error. Perhaps copying the whole row is too much - I only need to copy data up to column AO.

 

[gsistek]

I have no problems with the following code as long as there is a sheet named "Sheet3". Otherwise, change that to suit.

Private Sub CommandButton1_Click()
Dim rngData As Range
Set rngData = Rows(ActiveCell.Row)
rngData.Copy
Sheets("Sheet3").Range("B5").PasteSpecial _
    Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

 

[hunter9002]

Ah, looks like I had some syntax issues. Colons, commas, equal signs. Blah.Thanks again! Have an excellent summer.

 

[gsistek]

Glad I could help.