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 https://stackoverflow.com/questions/13174916/transpose-a-range-in-vba — Teamothy, Oct 04 '19 at 12:25

score 2 · Accepted Answer · answered Oct 04 '19 at 12:25

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 04 '19 at 12:25

SJR

22,986
6
18
26

1

Got it thanks, just need to put the 1 in Cells(Rows.Count, 1) as a 3 for column C – Forkinator9000 Oct 04 '19 at 12:28
One thing I omitted to mention is that this code will overwite as it always pastes to A1. – SJR Oct 04 '19 at 16:45

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

1 Answers1