Help please! Loops? For Each? Variables?

[Guest]

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

 

[Bob Phillips]

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

 

[Don Guillett]

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