Help please! Loops? For Each? Variables?

I have recorded the following code which essentially types in a branch number
(7013) into a worksheet, copies the worksheet into a new workbook, and then
saves the workbook into a designated directory usng the branch number in the
file name (7013 Bonus Summary), and finally closes the file.

I have 120 branches and rather than copy this block of code 120 times, I
know that there is a better way to do this using variables and looping.

Can someone please point me in the right direction.


Sheets("Branch Bonuses").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "7013"
Range("C3").Select
Sheets("Branch Bonuses").Select
Sheets("Branch Bonuses").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

Application.DisplayAlerts = False

ChDir _
"\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
2004-2005\Bonuses\Northern\Area 1"
ActiveWorkbook.SaveAs Filename:= _
"\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
2004-2005\Bonuses\Northern\Area 1\7013 Bonus Summary.xls" _

, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Application.DisplayAlerts = True

Application.DisplayAlerts = False

Workbooks("7013 Bonus Summary.xls").Close

Application.DisplayAlerts = True




--
Thanks
Roger

Put it in a sub with the branch as a parameter, and call like

Branc hUpdate "7013"

etc.


Sub BranchUpdate(branch As String)
Dim sFilename As String
Const kDir = _
"\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
2004-2005\Bonuses\Northern\Area 1"

sFilename = branch & " Bonus Summary.xls"
Sheets("Branch Bonuses").Select
Range("C2").Value = branch
Sheets("Branch Bonuses").Select
Sheets("Branch Bonuses").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

Application.DisplayAlerts = False

ChDir kDir
ActiveWorkbook.SaveAs Filename:=kDir & "\" & sFilename

Application.DisplayAlerts = True
Application.DisplayAlerts = False

Workbooks(sFilename).Close

Application.DisplayAlerts = True
End Sub

--
HTH

Bob Phillips

"Roger" <[email protected]> wrote in message
news:[email protected]...
> I have recorded the following code which essentially types in a branch
number
> (7013) into a worksheet, copies the worksheet into a new workbook, and
then
> saves the workbook into a designated directory usng the branch number in
the
> file name (7013 Bonus Summary), and finally closes the file.
>
> I have 120 branches and rather than copy this block of code 120 times, I
> know that there is a better way to do this using variables and looping.
>
> Can someone please point me in the right direction.
>
>
> Sheets("Branch Bonuses").Select
> Range("C2").Select
> ActiveCell.FormulaR1C1 = "7013"
> Range("C3").Select
> Sheets("Branch Bonuses").Select
> Sheets("Branch Bonuses").Copy
> Cells.Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Range("A1").Select
>
> Application.DisplayAlerts = False
>
> ChDir _
> "\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
> 2004-2005\Bonuses\Northern\Area 1"
> ActiveWorkbook.SaveAs Filename:= _
> "\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
> 2004-2005\Bonuses\Northern\Area 1\7013 Bonus Summary.xls" _
>
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
>
> Application.DisplayAlerts = True
>
> Application.DisplayAlerts = False
>
> Workbooks("7013 Bonus Summary.xls").Close
>
> Application.DisplayAlerts = True
>
>
>
>
> --
> Thanks
> Roger

something like this. UN tested but you will get the idea.

for each c in range("mylistofbrances")

with Sheets("Branch Bonuses")
.. Range("C2")=c
.. Cells.Copy
.. cells.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
end with

Application.DisplayAlerts = False
ChDir _
"\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
2004-2005\Bonuses\Northern\Area 1"
ActiveWorkbook.SaveAs Filename:= _
"\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
2004-2005\Bonuses\Northern\Area 1\" & c & "Bonus Summary.xls" _

, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

activeworkbook.Close

Application.DisplayAlerts = True

next c

--
Don Guillett
SalesAid Software
[email protected]
"Roger" <[email protected]> wrote in message
news:[email protected]...
> I have recorded the following code which essentially types in a branch
number
> (7013) into a worksheet, copies the worksheet into a new workbook, and
then
> saves the workbook into a designated directory usng the branch number in
the
> file name (7013 Bonus Summary), and finally closes the file.
>
> I have 120 branches and rather than copy this block of code 120 times, I
> know that there is a better way to do this using variables and looping.
>
> Can someone please point me in the right direction.
>
>
> Sheets("Branch Bonuses").Select
> Range("C2").Select
> ActiveCell.FormulaR1C1 = "7013"
> Range("C3").Select
> Sheets("Branch Bonuses").Select
> Sheets("Branch Bonuses").Copy
> Cells.Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Range("A1").Select
>
> Application.DisplayAlerts = False
>
> ChDir _
> "\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
> 2004-2005\Bonuses\Northern\Area 1"
> ActiveWorkbook.SaveAs Filename:= _
> "\\cdfinnsrv01\cd-branch-dept\Finance\Monthly Reporting
> 2004-2005\Bonuses\Northern\Area 1\7013 Bonus Summary.xls" _
>
> , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
>
> Application.DisplayAlerts = True
>
> Application.DisplayAlerts = False
>
> Workbooks("7013 Bonus Summary.xls").Close
>
> Application.DisplayAlerts = True
>
>
>
>
> --
> Thanks
> Roger