VBA Paste to last row

[thedeadzeds]

Guys, How do i change the below to paste as value to the last row of the Sheets("BA Audi SMOT NC Values")

Sheets("BA Polk Audi Serv & MOT N Calls").Select    Cells.Select
    Selection.Copy
    Sheets("BA Audi SMOT NC Values").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Kamolga]

Hi,

You need to be more specific with the range to copy:

Cells.copy copy the entire page and can only be paste in a1.
If we can check column A to identify the last row, then we would use (you can adapt the letter)

[COLOR=#3D3D3D][FONT='inherit']Dim LastRow as long

LastRow = Worksheets[/FONT][/COLOR][COLOR=#333333]("BA Polk Audi Serv & MOT N Calls")[/COLOR][COLOR=#3D3D3D][FONT='inherit'].Cells([/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit']Worksheets[/FONT][/COLOR][COLOR=#333333]("BA Polk Audi Serv & MOT N Calls")[/COLOR][COLOR=#3D3D3D][FONT='inherit'].Rows.Count, "A").End(xlUp).Row[/FONT][/COLOR]

So your code would look like

Sub test()
Dim LastRow As Long
 LastRow = Worksheets("BA Audi SMOT NC Values").Cells(Worksheets("BA Audi SMOT NC Values").Rows.Count, "A").End(xlUp).Row
    Sheets("BA Audi SMOT NC Values").Select
    Range("A"&Lastrow+1).Select

 LastRow = Worksheets("BA Polk Audi Serv & MOT N Calls").Cells(Worksheets("BA Polk Audi Serv & MOT N Calls").Rows.Count, "A").End(xlUp).Row
    Sheets("BA Polk Audi Serv & MOT N Calls").Rows("1:" & LastRow).Copy
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

 

[PCL]

"to paste as value to the last row " or the row AFTER the last row ???

 

[PCL]

If yes to previous question, see next code

Sub Test()
    Sheets("BA Polk Audi Serv & MOT N Calls").Cells(1, 1).CurrentRegion.Copy
    Sheets("BA Audi SMOT NC Values").Cells(Rows.Count, 1).End(3)(2).PasteSpecial _
          Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[Kamolga]

"to paste as value to the last row " or the row AFTER the last row ???

To the row

[COLOR=#333333]Range("A"&Lastrow).Select[/COLOR]

To the next

[COLOR=#333333]Range("A"&Lastrow+1).Select[/COLOR]