how do I write a request to 'Save As' Macro

[CRAIG K]

Hi, I have recorded a macro which opens a new workbook called 'wizdatasave',
then the macro selects and copies 2 worsheets from another open workbook and
pastes them into the newly open workbook 'wizdatasave'. What code do I enter
into the macro to automate the 'Save As' function? I need to save the
'wizdatasave' workbook as something different each time it is opened.

regards

Craig

 

[Dave Peterson]

Maybe you could use the date/time appended to the name:

Dim WizWkbk as workbook
set wizwkbk = workbooks.open("C:\somefolder\wizdatasave.xls")
'do the work

wizwkbk.saveas filename:="C:\someexistingfolder\wizdata" _
& format(now, "yyyymmdd-hhmmss") & ".xls", _
fileformat:=xlworkbooknormal

 

[CRAIG K]

Hi Dave, I actually need to call teh work book by a different number which
may not be incremental so I would have to enter it manually at the save as
stage such as "2350wiz.xls". Here is the actual macro to show you what I have
and maybe you could show me how to append the 'save as' bit to the end

regards


Craig

Workbooks.Open Filename:="C:\excel run data\data\WIZSAVEDATA.xls"
Windows("JETLOOPR.XLS").Activate
Sheets("Ten Minutes").Select
Sheets("Ten Minutes").Copy Before:=Workbooks("WIZSAVEDATA.xls").Sheets(1)
Windows("JETLOOPR.XLS").Activate
Sheets("Hourly").Select
Sheets("Hourly").Copy Before:=Workbooks("WIZSAVEDATA.xls").Sheets(1)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

 

[Bob Phillips]

Const FILE_BASE As String = "C:\excel run data\data\WIZSAVEDATA"

Set wb = Workbooks.Open(Filename:=FILE_BASE & ".xls")
Windows("JETLOOPR.XLS").Activate
Sheets("Ten Minutes").Select
Sheets("Ten Minutes").Copy
Before:=Workbooks("WIZSAVEDATA.xls").Sheets(1)
Windows("JETLOOPR.XLS").Activate
Sheets("Hourly").Select
Sheets("Hourly").Copy Before:=Workbooks("WIZSAVEDATA.xls").Sheets(1)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ans = InputBox("Supply filename increment")
wb.SaveAs FILE_BASE & ans & ".xls"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

If you want to type in the name of the file, then why not just hit ctrl-s or
File|SaveAs.

But you could show that same dialog with a line:
Application.Dialogs(xlDialogSaveAs).Show

 

[CRAIG K]

thanks, problem sorted

regards

Craig

If you want to type in the name of the file, then why not just hit ctrl-s or
File|SaveAs.

But you could show that same dialog with a line:
Application.Dialogs(xlDialogSaveAs).Show