VBA Cleanup

F

FrankB

I realilze the code below is lengthly, however there must
be a way for me to shorten this code using a loop or
something like that. Any suggestions.

Thanks
Frank

Sub SaveMasterToAllPMFiles()
'
' Macro2 Macro
' Macro recorded 9/12/2003 by Frank J. Borger Intuit
'

'
Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "1040"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\1040 Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "COFI"
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\COFI Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "ETX"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\ETX Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "FAS"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\FAS Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "INSTALL"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\INSTALL Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "MTT"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\MTT Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "OLT"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\OLT Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "PCDM"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\PCDM Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "PRO"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\PRO Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "STATE"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\STATE Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "TAXSVS"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\TAXSVS Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "TCCP"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\TCCP Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "TPS"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\TPS Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "TTMAC"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\TTMAC Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "TTW"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\TTW Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "TTWIN"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\TTWIN Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap
Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "VOU"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\VOU Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close






End Sub
 
Top