SaveAs Method with Filename Derived From cell reference

[wmcnabb084]

I am trying to use a cell reference to have excel automatically Save a worksheet using a cell reference as the workbook name. When I step through the code and use the immediate window I get the correct name for the file (fname in this case) but as soon as Excel tries to save the file, I get an error.

Here is the code I am using:

fname = Sheet3.Range("I1").Value
ChDir "S:\Op Costs\Budget 2013\Data"
MsgBox "The actice file will be saved as " & fname
ActiveWorkbook.SaveAs filename:= _
"S:\Op Costs\Budget 2013\Data\" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Any help would be most appreciated...

 

[memar_one]

Change this line:
fname = Sheet3.Range("I1").Value

To:
fname = Sheets("Sheet3").Range("I1").Value

Including Option Explicit in your code might help catch this in the future.



Also you misspelled "active" in your message box.

 

[wmcnabb084]

Did not help sorry. Still getting a Runtime 1004 Method Method 'SaveAs' of object '_Workbook' failed

 

[memar_one]

try doing:

Dim wb As Workbook
Set wb = ActiveWorkbook

fname = wb.Sheets("Sheet3").Range("I1").Value
     ChDir "S:\Op Costs\Budget 2013\Data"
     MsgBox "The actice file will be saved as " & fname
     wb.SaveAs filename:= _
         "S:\Op Costs\Budget 2013\Data\" & fname & ".xlsm", FileFormat:= _
         xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Maybe it has something to do with how your ChDir is changing directories. Never used that before. Why do that? (wondering)

 

[wmcnabb084]

Still getting same SAveAs Method error.

Current code module:

Sub TestSaveAsOPEX()
'
'
Dim fname As Variant
Dim wb As Workbook
Set wb = ActiveWorkbook
fname = wb.Sheets("Input Sheet").Range("I1").Value
ChDir "S:\Op Costs\Budget 2013\Data"
MsgBox "The active file will be saved as " & fname
wb.SaveAs filename:= _
"S:\Op Costs\Budget 2013\Data\" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

I have r/w access to path mentioned and can run this code fine if I hard code a file name such as

Sub TestSaveAsOPEX()
'
'
Dim fname As Variant
Dim wb As Workbook
Set wb = ActiveWorkbook
' fname = wb.Sheets("Input Sheet").Range("I1").Value
ChDir "S:\Op Costs\Budget 2013\Data"
MsgBox "The active file will be saved as " & fname
wb.SaveAs filename:= _
"S:\Op Costs\Budget 2013\Data\testFile.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

 

[wmcnabb084]

Aghhhhhhhhhhhhhhhhh

The name in Sheet3 I1 had an '/' character in the name so I will have to check that first and substitute the '/' character with a '_'

Thank you so much for your effort and help!

WM