SaveAs using two cells for filename

[David]

I'm trying to save a file with two cells of data to create the filename. Here
is the code:
Dim fname
With ActiveWorkbook
fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls"
..SaveAs fname
End With

I'm getting stopped on the fname line. I don't think I have the combination
right. Can you help?

 

[Dave Peterson]

Dim fname as String
With ActiveWorkbook.worksheets(1)
fname = .Range("B4").Value & .Range("E6") & ".xls"
.SaveAs fname
End With

You missed the .worksheets(1) in front of E6.

Any chance that one of those cells contains a date--and that the value has
slashes in it (12/31/2005)?

If yes, this might help later...

Dim fname as String
With ActiveWorkbook.worksheets(1)
fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls"
.SaveAs fname
End With

If not, just ignore it.

 

[David]

I've almost got it...it saved the file in the right format, but saved it deep
in my settings. I want to save it to a particular path, which was earlier in
the code, but it is not working now...I always want to save in the directory
below. By the way, you were right, the E6 was a DATE. Can I put spaces in the
date format like mm dd yyyy? Here is what I have:

Dim fname
With ActiveWorkbook.Worksheets(1)
fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls"
ChDir "C:\Franchise_GPC\Ben Info\Big Picture 2005\"
..SaveAs fname

 

[David]

I got it....here's what I ended up with...

Dim fname As String
With ActiveWorkbook.Worksheets(1)
fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls"
..SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname
End With

Thanks again for the help!!

 

[Dave Peterson]

You didn't use "mm dd yyyy" as your format <bg>.

I don't like spaces in my filename, and it might make it easier to read if you
use underscores:

fname = .Range("B4").Value & "_" & _
Format(.Range("E6").Value, "yyyy_mm_dd") & ".xls"

But this is just a personal preference.

 

[David]

Thank you again....!!

You didn't use "mm dd yyyy" as your format <bg>.

I don't like spaces in my filename, and it might make it easier to read if you
use underscores:

fname = .Range("B4").Value & "_" & _
Format(.Range("E6").Value, "yyyy_mm_dd") & ".xls"

But this is just a personal preference.