MACRO to save current worksheet with variable filename

  • Thread starter Thread starter Wes_A
  • Start date Start date
W

Wes_A

I need to save the currently active sheet as a new workbook in a different
directory (folder). The filename is volatile and is contained in a cell on
the currently open sheet.
e.g. September, October ....
The saved file needs to be an Excel workbook format *.xls or *.xlsb
I am trying to write a MACRO to automate this file save and then retuirn to
the currently open sheet.
Can anyone help?
 
Try

Dim flToSave As String
Dim flName As String
Dim flFormat As Long

flFormat = ActiveWorkbook.FileFormat
flName = Sheets("Sheetname").Range("A1").Text & ".xls"

'If it is same as the current workbook
flToSave = ActiveWorkbook.Path
OR
'If it is a different path assign to that variable
flToSave = "m:\contract\"

ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat
OR
ActiveWorkbook.SaveCopyAs flToSave & "\" & flName


If this post helps click Yes
 
Back
Top