Prev: MAC OS Excel 2008 Visual Basic Programming
Next: macro to filter data based on selected data
From: Plum on 19 Apr 2010 02:50
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...
From: Jacob Skaria on 19 Apr 2010 03:11
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...
From: ozgrid.com on 19 Apr 2010 03:36
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" <Plum(a)discussions.microsoft.com> wrote in message
news:00378CC9-BA08-46F5-9E75-439835AD47F7(a)microsoft.com...
> 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...

From: Plum on 19 Apr 2010 21:35
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" <Plum(a)discussions.microsoft.com> wrote in message
> news:00378CC9-BA08-46F5-9E75-439835AD47F7(a)microsoft.com...
> > 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...
>
 | 
Pages: 1
Prev: MAC OS Excel 2008 Visual Basic Programming
Next: macro to filter data based on selected data