Saving a workbook with a makro

W

Wombat

I am writing a macro, a part of which is saving the current workbook. I dont
want the name of the file to be the same everytime... The path name should
stay the same but the name of the file should be taken from a cell in the
workbook (C5).

How can I write this into the macro?

ActiveWorkbook.SaveAs Filename:= _
"P:\***path name***\Name of the file.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Thanks for any help
 
M

Mike H

Hi,

Set the variable mypath to the correct one and change Sht to the correct
worksheet and try this

mypath = "C:\"
Set sht = Sheets("Sheet1")

ActiveWorkbook.SaveAs Filename:= _
mypath & sht.Range("C5").Value _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
J

Jacob Skaria

Edit the sheetname.....

Dim strPath as String
Dim strFile as String

strPath = ActiveWorkbook.Path
strFile = Sheets("Sheetname").Range("C5").Text & ".xls"

ActiveWorkbook.SaveAs Filename:= strPath & "\" & strFile, FileFormat:=xlNormal
 

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

Top