You would usually add Jake's line of code before you close your workbook. But
since you do your paste after you close the workbook, that won't work for your
existing code.

But this did:

Option Explicit
Sub testme01()
Selection.SpecialCells(xlCellTypeLastCell).Select
Range(Selection, Cells(1)).Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Range("A1").Select
ActiveSheet.Paste
End Sub

But I think I'd take a slightly different approach. You rely on the window to
be active when you close one workbook to be open to the worksheet where you want
to paste.

If you could specify the "from" worksheet and the "to" worksheet, then you could
do something like this:

Option Explicit
Sub testme01B()

Dim FromWks As Worksheet
Dim ToWks As Worksheet

Set FromWks = ActiveSheet
Set ToWks = Workbooks("book2").Worksheets("sheet1")

With FromWks
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=ToWks.Range("a1")

Application.CutCopyMode = False
.Parent.Close savechanges:=False 'true '????
End With

End Sub

Ian M wrote:

Thanks Jake

However I'm not sure exactly where I should put you line of code in my
macro.

My macro is:

Selection.SpecialCells(xlCellTypeLastCell).Select
Range(Selection, Cells(1)).Select
Selection.Copy
ActiveWorkbook.Close
Range("A1").Select
ActiveSheet.Paste

I've tried putting your line before or after but it doesn't seem to
work. I must be doing something wrong?

Thanks again for your help.

Kind regards

Ian M

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson