Save as variable file name

P

Plum

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...
 
J

Jacob Skaria

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
 
O

ozgrid.com

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
 
P

Plum

thank you both that is fabulous!

ozgrid.com said:
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
 

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