Hey all.

Having some issues with this, and wanted to see if you have any ideas.

Essentially what I am doing is looping through 9 sheets and excecuting some
code to create pivot tables on each sheet.

For my code to work properly I need a few dimensions assigned, 'TbName' and
'RegionSelect'

What I have now is this just reiterated 9 times, with tb name being 9
different names and 9 different sheets being selected

Sheets("Sheet").Select
TbName = "Name of region"
RegionSelect = ActiveSheet.Name
My_Macro


So now I have come up with this code below:

Public TbName As String
Public RegionSelect As String
Dim wSht As Integer

Sheets(1).Select
wSht = ActiveSheet.Index
Do While wSht < 10
If wSht = 1 Then
Sheets(wSht).Select
Else
End If

Sheets(wSht).Select
TbName = ActiveSheet.Name
'Code that does stuff will go here
wSht = wSht + 1

Loop


What I need is a way to assign 'RegionSelect' with the proper region for
each sheet
The only way I can think of doing this is to create 9 If statements like:

If activesheet.name = "sheet 1" Then
Regionselect = "Region1"
End if

Is there a better way to do this?