Copy cells in adding a row in another workbook

Question

So i have to copy cells A1, B2 and C3 from one workbook and add a row in anotherworkbook(in the last line) with theses values in the columns A,B,C.

Here's what i got so far, i think i'm close but i cant finish. I havo no idea whats wrong with this syntax "Set lastrow = wNew.Cells.(Rows.Count, "A").End(xlUp).Row + 1" that seens to be the problem

Sub Botão1_Clique()

Dim wks As Worksheet
Dim wNew As Worksheet
Dim y As Workbook
Dim lastrow As Long

Application.ScreenUpdating = False
Set wks = ActiveSheet

Set y = Workbooks.Open("Y:\teste.xlsx")

Set wNew = y.Sheets("GERAL")

Set lastrow = wNew.Cells.(Rows.Count, "A").End(xlUp).Row + 1


wks.Cells(1, 1).Copy
wNew.Cells(lastrow, 1).PasteSpecial Paste:=xlPasteValues
wks.Cells(2, 2).Copy
wNew.Cells(lastrow, 2).PasteSpecial Paste:=xlPasteValues
wks.Cells(3, 3).Copy
wNew.Cells(lastrow, 3).PasteSpecial Paste:=xlPasteValues



  Application.ScreenUpdating = True


End Sub

I also would like to close the Y:\teste.xlsx workbook, and display a message saying "ROW ADDED"

CallumDA CallumDA · Accepted Answer · 2017-02-16T16:19:09

You do a good job properly referencing Workbooks and Worksheets but also make sure you fully qualify Cells and Rows. They are properties of the worksheet object I.e. ThisWorkbook.Worksheets("..").Rows

Sub Botão1_Clique()
    Dim wks As Worksheet, wNew As Worksheet
    Dim y As Workbook
    Dim lastrow As Long

    Application.ScreenUpdating = False

    Set wks = ActiveSheet
    Set y = Workbooks.Open("Y:\teste.xlsx")
    Set wNew = y.Sheets("GERAL")

    With wNew
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(lastrow, 1).Value = wks.Cells(1, 1)
        .Cells(lastrow, 2).Value = wks.Cells(2, 2)
        .Cells(lastrow, 3).Value = wks.Cells(3, 3)
    End With

    'extra code as requested        
    y.Close True 'save changes if TRUE
    MsgBox "ROW ADDED"

    Application.ScreenUpdating = True
End Sub

Copy cells in adding a row in another workbook

1 Answers