Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help please! Loops? For Each? Variables?
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
Using For - Next Loops in VB | New Users to Excel | |||
Loops | Excel Programming | |||
Loops etc. | Excel Programming | |||
help with loops | Excel Programming |