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

Question

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

Check here stackoverflow.com/questions/13174916/transpose-a-range-in-vba — Teamothy
– Teamothy, Commented Oct 4, 2019 at 12:25

SJR · Accepted Answer · 2019-10-04 12:25:33Z

2

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

answered Oct 4, 2019 at 12:25

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Forkinator9000 Over a year ago

Got it thanks, just need to put the 1 in Cells(Rows.Count, 1) as a 3 for column C

SJR Over a year ago

One thing I omitted to mention is that this code will overwite as it always pastes to A1.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related