Dynamically setting Dimensions in a VBA loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Not sure I fully understand but you can create worksheet local names like so

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Sheet1" Then
ActiveWorkbook.Names.Add Name:="'" & sh.Name & "'!Region", _
RefersTo:="='" & sh.Name & "'!$A$3:$A$10"
End If
Next sh


change the range to suit

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
You lost me on that one, but let me explain a bit better here.

I have 2 completely different values that need to be determined. One is
based off the name of the sheet, so there is no worries there. So if sheet1
equalled "Jupiter" then my Dim TbName would = Jupiter. However the second
variable is based off that name, so if TbName = Jupiter, then Dim
RegionSelect = "5th Planet".

The problem lies in that I dont know how to set RegionSelect without using
several If statements to figure out what it should be.
 
Perhaps using the index value of the sheet.

Dim v as Variant, TbName as String
v = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")

TbName = v(activesheet.Index-1)
 
I think Tom twigged it then using an array.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top