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
"Neil Holden" wrote:
> 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
>
>
>
>
|
Similar Threads
