I think excel gets confused with the activeworkbook or activesheet.

In either case, I'd do:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim NewWkbk As Workbook

Set NewWkbk = Workbooks.Add(1) 'single sheet

Me.Worksheets("Sheet1").UsedRange.Copy _
Destination:=NewWkbk.Worksheets(1).Range("a1")

NewWkbk.SaveAs Filename:="C:\NewCopiedBook.xls", _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

NewWkbk.Close savechanges:=False

'uncomment the next line when you're testing
'Cancel = True
End Sub

Sara wrote:

Hi,
I want to copy the data in one worksheet into an other worksheet in a new
workbook.
This is my code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Activate
ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\NewCopiedBook.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

I can see the cells being selected and copied. And a new workbook created
and the cells being pasted. And then the new workbook being closed.
But when I open it there is no data in the cells.

When i debug I can see that the worksheet.usedrange is empty.
The data in the "original" workbook from has just been pasted, probably not
saved. But I tried to save the "original" workbook before the code above.

What am I missing?

/Sara

--

Dave Peterson