save as

[flow23]

Is it possible to have a macro that will save as a new file with value from
a particular field

say the current file is Report.xls
say the value in sheet1 and range B3 is "14/03/2006"

The file should save as " Report 14/03/2006 .xls"

 

[Ardus Petus]

with Workbooks("Report.xls")
.SaveAs format(.Worksheets("Sheet1").Range("B3").value, "dd-mm-yyyy") &
".xls"
end with

I modified the date format because / are not authorized in file names.

HTH

 

[flow23]

thanks it works great

can I also add the location of the file name to a particualr folder on the
network?

 

[Jim May]

The original Filename portion "Report" is not being picked up
in the new filename; Only the date.
TIA,

 

[Jim May]

OK,
After modifying line to: (.name being added)

.SaveAs .name format(.Worksheets("Sheet1").Range("B3").value, "dd-mm-yyyy") &
It works as expected.
Tks,

 

[Dave Peterson]

One way:

with Workbooks("Report.xls")
.SaveAs "\\yoursharename\yourfoldername1\yourfoldername2\" & "Report " & _
format(.Worksheets("Sheet1").Range("B3").value, "dd-mm-yyyy") & ".xls"
end with

 

[flow23]

Many thanks

One way:

with Workbooks("Report.xls")
.SaveAs "\\yoursharename\yourfoldername1\yourfoldername2\" & "Report " & _
format(.Worksheets("Sheet1").Range("B3").value, "dd-mm-yyyy") & ".xls"
end with

 

[Dave Peterson]

I bet you'd want to strip the .xls out of the .name property, too.