Save as variable file name
 

Hi there,
I've got a list of 55 cities, and I'd like a macro to go down the list one
by one, and save the file with each city as the file name, so that in the end
I have 55 different files, each called city1.xls and city 2.xls and so forth.
I'm sure this is possible but not sure where to start!
Thanks for your help...

Save as variable file name
 

Try the below...Edit the workbooknames ..Assume the workbook is open

Sub Macro()
Dim cell As Range, wb As Workbook
Set wb = Workbooks("Whichworkbook.xls")

Application.DisplayAlerts = False
'Specify the range in which the cities are mentioned...
For Each cell In Workbooks("book3.xls").Sheets("Sheet3").Range("A5: A8")
If cell.Text < "" Then
wb.SaveAs "d:\" & cell.Text & ".xls", xlNormal
End If
Next
Application.DisplayAlerts = True
End Sub


--
Jacob (MVP - Excel)


"Plum" wrote:

Hi there,
I've got a list of 55 cities, and I'd like a macro to go down the list one
by one, and save the file with each city as the file name, so that in the end
I have 55 different files, each called city1.xls and city 2.xls and so forth.
I'm sure this is possible but not sure where to start!
Thanks for your help...

Save as variable file name
 

Assumes file names are in Column "A".

Sub SaveAsX()
Dim rCell As Range
Dim strPath As String

For Each rCell In Range("A1:A54")
ThisWorkbook.SaveAs _
"YOUR PATH HERE/" & rCell
Next rCell
End Sub



--
Regards
Dave Hawley
www.ozgrid.com


"Plum" wrote in message
...
Hi there,
I've got a list of 55 cities, and I'd like a macro to go down the list one
by one, and save the file with each city as the file name, so that in the
end
I have 55 different files, each called city1.xls and city 2.xls and so
forth.
I'm sure this is possible but not sure where to start!
Thanks for your help...

Save as variable file name
 

thank you both that is fabulous!

"ozgrid.com" wrote:

Assumes file names are in Column "A".

Sub SaveAsX()
Dim rCell As Range
Dim strPath As String

For Each rCell In Range("A1:A54")
ThisWorkbook.SaveAs _
"YOUR PATH HERE/" & rCell
Next rCell
End Sub



--
Regards
Dave Hawley
www.ozgrid.com


"Plum" wrote in message
...
Hi there,
I've got a list of 55 cities, and I'd like a macro to go down the list one
by one, and save the file with each city as the file name, so that in the
end
I have 55 different files, each called city1.xls and city 2.xls and so
forth.
I'm sure this is possible but not sure where to start!
Thanks for your help...