Fixing my macro to copy a range to the next blank column?

Question

I need to copy a cell range into the next blank column in a separate sheet, every time the forms buttons for the macro is clicked.

Here's the code. The problem is it copies to the next blank row, and I need the next blank column. I have tried editing the line* in various ways and end up with errors, or no effect (e.g. replacing "Rows" with "Columns").

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

If found the base for the 'copy to next blank row VBA' here at SO, at the following link: Copy and Paste a set range in the next empty row

Thanks for any help, I'm stuck currently.

Sub TestCopyToDB()

    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet

    Set copySheet = Worksheets("sheet1")
    Set pasteSheet = Worksheets("sheet2")

    copySheet.Range("M1:M15").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Although @DavidZemens got it covered, you can check this out for additional info on Cells Property. — L42
@L42, thank you very much, that really was enlightening, as was DavidZemens's answer. I asked one question and learned much more than one answer thanks to both of you; SE rocks! (I tried up voting your answer but I can't, so I wanted to thank you here.) — Kim Blm
Np :) You can try again once you gained enough rep. Also, another way of thanking is to help the community if you can. — L42

David Zemens David Zemens · Accepted Answer · 2014-09-25T01:30:53

The Cells method has two arguments, row and column, i.e.,

Cells(1,1)   '<~~ equivalent to cell "A1"
Cells(1,3)   '<~~ equivalent to cell "C1"
Cells(10,13) '<~~ equivalent to cell "M10"

The Offset method works similarly, with two arguments, row_offset, and column_offset, so:

.Offset(1,1)   '<~~ returns the cell one row below, and one column to the right
.Offset(-1, 3) '<~~ returns the cell one row above, and 3 columns to the right

Making some adjustments, and change the .End(xlUp) (for rows) to .End(xlToLeft), gives this:

With pasteSheet
    .Cells(1, .Columns.Count).End(xlToLeft).Offset(0,1).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End With

Fixing my macro to copy a range to the next blank column?

1 Answers