Sub Report()

Dim Nm As String, newBook As Workbook, ThisBook As Workbook

If MsgBox("Do you want to create report?", vbQuestion + vbYesNo) =
vbYes Then

Set ThisBook = ActiveWorkbook
Set newBook = Workbooks.Add
Nm = Day(Now) & "_" & Month(Now) & "_" & Year(Now) & Time

With newBook
..SaveAs FileName:="C:\Destop\Report\" & Nm

'code for copy sheets from ThisBook to newBook here

End With

MsgBox "New Report Created and Saved As" & Workbooks(Nm).Name

Set newBook = Nothing
Set ThisBook = Nothing

End If

End Sub

You just needed to End With. And also when using Object variable (eg Wb
variables), ALWAYS Set it back to Nothing, or you could have some
surprises. Object variables can creat all sorts of troubles.

Hope this works, I haven't had time to test it and I'm still a beginner
really...


---
Message posted from http://www.ExcelForum.com/