Alistair,

Depends what event triggers first prompt.

Basically you'll need to use MsgBox and SaveAs. Familiar with that?

something like

Sub Report ()

Dim Nm as String

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

'create new Wb
'copy Ws to new Wb

newWb.SaveAs (Filename:= "" & Now, etc... look into XL help fo
details)

Nm = newWb.Name

MsgBox "New report created and saved as " & Nm

EndIf

You should refer to XL help for creating a new Wb (can't remembe
exactly how right now, maybe Application.Workbooks.Add?)

Did that help

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