Selecting worksheets without hardcoding
 

If the sheet name is the branch number, you could use

Worksheets(Activecell.Value).Activate


If not you could use something like

Select Case Actvecell.Value
Case 1: Worksheets("Western").Activate
Case 2: Worksheets("Mid").Activate
'etc
End Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dumbguy" wrote in message
...
How can I select multiple sheets without coding the sheetnames?
For instance, I have 10 branches servicing 50 states.
Branch 1 serves CA and AZ, Branch 2 serves UT,NV,MT,ID, etc.
I have a sheet with a lookup file that looks like this:
1 AZ
1 CA
2 UT
2 NV
2 MT
How can I select these sheets in an array upon change of the branch
number? Using the recorder, it always wants to hardcode the sheet name. I
would like to save all the states and a summary page for each branch.

Thanks!

Selecting worksheets without hardcoding
 

Can I activate more than one at a time though? What I want to do is for instance select CA,AZ and Branch 1 Summary at the same time, and then copy to a new workbook for distribution to branch 1. The code in the recorder looks like this

Sheets(Array("Branch 102 Totals", "AL", "FL", "GA", "MS", "102 Competition")).Cop
ChDir "V:\Branch and Corporate\BR102
ActiveWorkbook.SaveAs Filename:=
"V:\Branch and Corporate\BR102\Branch102BARModel.xls", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=Fals

What I can't figure out is how to create the Sheets(Array) Function without hardcoding the names in there. Since we periodically re-assign states, I want Excel to be able to pick to right sheets to save to each branch based on a lookup table rather than editing the code every time

Thanks

Thanks