Save as variable file name

  • Thread starter Thread starter Plum
  • Start date Start date
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...
 
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
 
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
 
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
Click to expand...
 
Back
Top