CODEWITHSUNDEEP
excelvba
Kim Blm
Kim Blm

Reputation: 63

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

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").

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

Upvotes: 6

Views: 4149

Answers (1)

David Zemens
David Zemens

Reputation: 53663

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

Upvotes: 10

Related Questions