Copying and Pasting Macro from one worksheet to another

N

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
 
B

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
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top