save file as different name until cell is blank
 

I have a worksheet that needs to be saved in anywhere from 1 to 6 different
locations depending on the contents of cells AH1 through AH6 (this is where
the filenames are stored). I want the macro to save to all filenames in
this range, but I don't want a runtime error when one of the cells is blank,
for example if there are only 4 filenames instead of 6. Presently, my code
is

Sub Save_to_root()
Dim fname
With ActiveWorkbook
fname = .Worksheets("Daily").Range("AH1").Value & ".xls"
..SaveAs fname
End With
End Sub

Can someone help with the adjustment? Thanks, Pam

save file as different name until cell is blank
 

Hi Pam

Try this
Note: I use SaveCopyAs

Sub test()
Dim fname As String

For I = 1 To 6
With ActiveWorkbook
If .Worksheets("Daily").Range("AH" & I).Value < "" Then
fname = .Worksheets("Daily").Range("AH" & I).Value & ".xls"
.SaveCopyAs fname
End If
End With

Next I
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Pam M" wrote in message ...
I have a worksheet that needs to be saved in anywhere from 1 to 6 different
locations depending on the contents of cells AH1 through AH6 (this is where
the filenames are stored). I want the macro to save to all filenames in
this range, but I don't want a runtime error when one of the cells is blank,
for example if there are only 4 filenames instead of 6. Presently, my code
is

Sub Save_to_root()
Dim fname
With ActiveWorkbook
fname = .Worksheets("Daily").Range("AH1").Value & ".xls"
.SaveAs fname
End With
End Sub

Can someone help with the adjustment? Thanks, Pam

save file as different name until cell is blank
 

newsreader probs - sorry for double post

"Patrick Molloy" wrote in message
...
Sub Save_to_root()
Dim fname
dim cell as range
With ActiveWorkbook
for each cell in .Worksheets("Daily").Range("AH1:AH6").Cells
if cell.value<"" then
fname = cell.Value & ".xls"
.SaveAs fname
end if
next
End With
End Sub


"Pam M" wrote:

I have a worksheet that needs to be saved in anywhere from 1 to 6
different
locations depending on the contents of cells AH1 through AH6 (this is
where
the filenames are stored). I want the macro to save to all filenames in
this range, but I don't want a runtime error when one of the cells is
blank,
for example if there are only 4 filenames instead of 6. Presently, my
code
is

Sub Save_to_root()
Dim fname
With ActiveWorkbook
fname = .Worksheets("Daily").Range("AH1").Value & ".xls"
.SaveAs fname
End With
End Sub

Can someone help with the adjustment? Thanks, Pam

save file as different name until cell is blank
 

Hi Patrick

Better two then nothing <g


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Patrick Molloy" wrote in message ...
newsreader probs - sorry for double post

"Patrick Molloy" wrote in message
...
Sub Save_to_root()
Dim fname
dim cell as range
With ActiveWorkbook
for each cell in .Worksheets("Daily").Range("AH1:AH6").Cells
if cell.value<"" then
fname = cell.Value & ".xls"
.SaveAs fname
end if
next
End With
End Sub


"Pam M" wrote:

I have a worksheet that needs to be saved in anywhere from 1 to 6
different
locations depending on the contents of cells AH1 through AH6 (this is
where
the filenames are stored). I want the macro to save to all filenames in
this range, but I don't want a runtime error when one of the cells is
blank,
for example if there are only 4 filenames instead of 6. Presently, my
code
is

Sub Save_to_root()
Dim fname
With ActiveWorkbook
fname = .Worksheets("Daily").Range("AH1").Value & ".xls"
.SaveAs fname
End With
End Sub

Can someone help with the adjustment? Thanks, Pam

save file as different name until cell is blank
 

These both worked well. I chose Rons because it allowed the file to stay
where it was while just depositing a copy which set me up for the next macro
save that I needed to do. Thanks so much to you both! Pam

"Ron de Bruin" wrote:

Hi Pam

Try this
Note: I use SaveCopyAs

Sub test()
Dim fname As String

For I = 1 To 6
With ActiveWorkbook
If .Worksheets("Daily").Range("AH" & I).Value < "" Then
fname = .Worksheets("Daily").Range("AH" & I).Value & ".xls"
.SaveCopyAs fname
End If
End With

Next I
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Pam M" wrote in message ...
I have a worksheet that needs to be saved in anywhere from 1 to 6 different
locations depending on the contents of cells AH1 through AH6 (this is where
the filenames are stored). I want the macro to save to all filenames in
this range, but I don't want a runtime error when one of the cells is blank,
for example if there are only 4 filenames instead of 6. Presently, my code
is

Sub Save_to_root()
Dim fname
With ActiveWorkbook
fname = .Worksheets("Daily").Range("AH1").Value & ".xls"
.SaveAs fname
End With
End Sub

Can someone help with the adjustment? Thanks, Pam