Stack different columns into one column on a different worksheet

Question

I want to copy all filled cells starting from C5 to column F of a different worksheet.

I referred to another post: Excel - Combine multiple columns into one column Modified the code based on my needs.

    Sub CombineColumns()
Dim Range1 As Range, iCol As Long, Range2 As Range, Check As Range, wks As Worksheets

Set Range1 = wks("T(M)").Range(Cells(5, 3), Cells(Cells(5, 3).End(xlDown).Row, Cells(5, 3).End(xlToRight).Column))
Set Check = wks("csv").Range("F1")
If IsEmpty(Check.Value) = True Then
Set Range2 = Check
Else
LastRow = wks("csv").Range("F" & Rows.Count).End(xlUp).Row
Set Range2 = wks("csv").Cells(LastRow, 6).Offset(1, 0)
End If
For iCol = 3 To Range1.Columns.Count
    wks("T(M)").Range(Cells(5, iCol), Cells(Range1.Columns(iCol).Rows.Count, iCol)).Copy
    wks("csv").Range2.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Next iCol
End Sub

But I kept getting the error message

"object doesn't support this method or property"

at the step of pasting. After I tried to qualify all the ranges, It says I didn't set the object variable.

Thank you so much for the help!

Shouldn't `.Range2` say `.Range` instead? or just use `Range2.` without the worksheet declaration as it is already implied from the [ActiveSheet] `Cells` reference. — braX, Aug 10 '19 at 21:49
Hi, I need to paste the copied cells into Range2, which is the first empty cell in column F of a different worksheet (instead of the current active sheet) — mmk541, Aug 10 '19 at 21:51
You really need to qualify each range and cell object with a sheet, or you will run into all kinds of confusion like this. — braX, Aug 10 '19 at 21:53
Since Range2 is variable, already set to a cell on sheet csv, drop the worksheet prefix. Ie just `Range2.PasteSpecial ...` — chris neilsen, Aug 11 '19 at 02:44

score 0 · Accepted Answer · answered Aug 12 '19 at 01:36

How about this?

Sub Transposes()

    ' Example just for hardcoded data
    Dim inputRange As Range
    Set inputRange = Sheets("Sheet1").Range("C5:F10").SpecialCells(xlCellTypeConstants)

    Dim outputCell As Range
    Set outputCell = Sheets("Sheet2").Range("A1")

    Dim cell As Range
    For Each cell In inputRange
        Dim offset As Long
        outputCell.offset(offset).Value = cell.Value
        offset = offset + 1
    Next cell

End Sub

Set the last row in ColumnF to be whatever you want, and if that changes dynamically, just use any one of the multiple techniques out there to find the last cell you need to copy/paste.

Stack different columns into one column on a different worksheet

1 Answers1