Saving and copying results

  • Thread starter Thread starter alistair01
  • Start date Start date
A

alistair01

I have built an excel file that has a number of worksheets in it, 1 ha
stats, 1 charts and the other 3 or 4 have lists generated by macros
The original data is in a sheet but linked to an access database. Wha
I want to be able to do is to have excel prompt the user as to whethe
they would like to get reports (yes/no) and if so copy the stats page
charts and the lists to another excel file and give that new file th
name "date time" Report. It would be helpfuf if a dialog box appeare
to tell the user where the file had been saved.
This might be a bit long winded but Im not sure of where to start
Please help
 
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
 
Sorry by this:

'create new Wb
'copy Ws to new Wb

I meant "here is your code to do this". Check Help for details on ho
to create a new Wb and copy Worksheets
 
Here is what ive done so far:
Sub Report()


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

Set newBook = Workbooks.Add
With newBook
.Title = "Report" & Now()
.SaveAs Filename:="Report" & Now()

Nm = newWb.Name
MsgBox "New Report Created and Saved As" & Nm

End If

Think ive missed somthing out and im not too sure about vba can anyon
help
 
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...
 
Back
Top