CODEWITHSUNDEEP
excelvba
mvasco
mvasco

Reputation: 5101

Copy a range from one sheet to the first empty row from another sheet

In an Excel Sheet I have inserted a button that executes a Macro. This is the code for the Macro:

Sub Button1_Click()
'
' Button1_Click Macro
'
' Keyboard Shortcut: Option+Cmd+y
'
    Range("A17:I34").Select
    Selection.Copy
    Sheets("ITEMS CONTROL").Select
    Range("A7").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A3:I9").Select
End Sub

The source sheet is "ORDEN DE COMPRA" and the sheet where I want to copy the selected range is "ITEMS CONTROL".

What I need is to copy the not empty rows from the selected range to the first empty row from the end sheet, and not to the range I have in my current code.

Thank you

Upvotes: 0

Views: 3509

Answers (1)

RogB
RogB

Reputation: 461

Would this work?

Sub Button1_Click()

    'This will select the whole range of cells starting in A17
    Sheets("ORDEN DE COMPRA").Select
    Range(Range("A17"), Range("A17").End(xlDown).End(xlToRight)).Copy

    'Now we need to find the first free row in the next sheet, assuming all rows have something on column A
    Sheets("ITEMS CONTROL").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False


End Sub

Upvotes: 1

Related Questions