Naming and saving a workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to be able to open a new workbook (which I can do) then save it as
workbook name + (value in B1 on open workbook, which will be a date).xls

How do I do this. I can open the workbook and name it and save it, but I
want to be able to add the date in B1 to the name.

Thanks.
 
Sub CreateDetailWB()
'
' CreateDetailWB Macro
' Macro recorded 2/20/2007 by Kevin Porter
'

'
Dim WBDate As String

WBDate = Workbooks("SRA Payroll Details.xlt").Sheets("List").Cells(A2)
Workbooks.Add
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
ChDir "\\dc\SRAO\Intuit\Payroll 2007"
ActiveWorkbook.SaveAs Filename:= _
"\\dc\SRAO\Intuit\Payroll 2007\SRA Detailed Payroll - " & WBDate &
".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

End Sub
 
Do you mean cell A2 in SRA Payroll Details contains the date you want to add
to the filename? If so, then:

WBDate = Format(Workbooks("SRA Payroll
Details.xlt").Sheets("List").Cells(A2), "yyyymmdd")
 
correction:

WBDate = Format(Workbooks("SRA Payroll
Details.xlt").Sheets("List").Range("A2").text, "yyyymmdd")
 
Seems to me, using the Text property to pass a date string to format would
increase the possibility of misinterpretation which would be avoided by
using Value

WBDate = Format(Workbooks("SRA Payroll
Details.xlt").Sheets("List").Range("A2").Value, "yyyymmdd")
 
Thank you. Worked like a dream.

Vergel Adriano said:
correction:

WBDate = Format(Workbooks("SRA Payroll
Details.xlt").Sheets("List").Range("A2").text, "yyyymmdd")
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Protection 2
Save As and new workbook new name used in existing code 8
Opening a list of Excel files and run a macro in each 17
Open and Save Files in Directory 1
Unwanted workbooks on startup 2
All Workbook Saved except This Workbook - Part2 3
Open and close workbooks 1
VBA insert column with two workbooks open 1

Back
Top