Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving and copying results
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving and copying results
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving and copying results
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving and copying results
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving and copying results
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Excel Pro 2003 iteration results | Excel Discussion (Misc queries) | |||
COPYING AND SAVING THE INFORMATION | Excel Discussion (Misc queries) | |||
copying formulas-displaying results | Excel Discussion (Misc queries) | |||
column copying and saving | Excel Discussion (Misc queries) | |||
Copying RESULTS of a FORMULA to another worksheet | Excel Programming |