CODEWITHSUNDEEP
excelvba
Forkinator9000
Forkinator9000

Reputation: 79

VBA copy column from sheet 1 and transpose paste into row in sheet 2

I am trying to write a macro to copy data from column C (to the last full row) and transpose paste that data into row 1 on sheet 2. I cannot get my code to work. I am getting a Run-time error 1004 for the Paste line of code.

Option Explicit
Sub ColumnRow()

    Dim lRow As Long

        lRow = Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet1").Range("C1" & lRow).Copy
        Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

End Sub

Upvotes: 0

Views: 553

Answers (1)

SJR
SJR

Reputation: 23081

Couple of things.

(1) Specify the sheet for lRow

(2) Syntax for range Range("C1" & lRow) was off - see below

Sub ColumnRow()

Dim lRow As Long

lRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'add sheet ref
Worksheets("Sheet1").Range("C1:C" & lRow).Copy 'specify full range
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

End Sub

Upvotes: 2

Related Questions