Copying and Pasting Macro from one worksheet to another

[Neil Holden]

Please help, we have created the following code that should copy a entire row
from the active worksheet, open an existing workbook and paste the copied
data into the next available row. However it is currently pasting it into
the next available row of the original workbook. Below you will find my
current code: Am I doing somthing stupid!!! Much appreciated.

Sub CopyLast()

Sheets("Summary").Range("A2").EntireRow.Copy

Application.ScreenUpdating = False

Workbooks.Open "C:\Documents and
Settings\david.cope\Desktop\CHR\Calcs.xls", UpdateLinks:=1

Windows("Calcs.xls").Activate

With Worksheets("Sheet1")

Dim NextRow As Range

Set NextRow = Sheet1.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)

NextRow.PasteSpecial (xlValues)

Application.CutCopyMode = False

Application.ScreenUpdating = True

End With

End Sub

 

[Barb Reinhardt]

Try this

Option Explicit

Sub CopyLast()
Dim aWB As Excel.Workbook
Dim aWS As Excel.Worksheet
Dim myWB As Excel.Workbook
Dim myWS As Excel.Worksheet

Set aWB = ActiveWorkbook
Set aWS = aWB.Sheets("Summary")

aWS.Range("A2").EntireRow.Copy

Application.ScreenUpdating = False

Set myWB = Workbooks.Open _
("C:\Documents and Settings\david.cope\Desktop\CHR\Calcs.xls", _
UpdateLinks:=1)

'Windows("Calcs.xls").Activate

Set myWS = myWB.Worksheets("Sheet1")

'With Worksheets("Sheet1")

Dim NextRow As Range

Set NextRow = myWS.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)

NextRow.PasteSpecial (xlValues)

Application.CutCopyMode = False

Application.ScreenUpdating = True

'End With

End Sub

HTH,
Barb Reinhardt

 

[Barb Reinhardt]

FWIW, when you used this line

Set NextRow = Sheet1.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)

It used Sheet1 of the workbook with the code.