Another loop & sheet question

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Say I have something simple like the code below:

for aaa = 1 To 2
For wksh = 1 To 5
Sheets.Add

...
' change active sheets in here
...
' Now I might want to go back to fifth sheet created
Next wksh
Next aaa
.... more code
' again, I may want to go back to the fifth sheet created
.... more code


The program above creates 10 sheets. I need to go back
later to some of the sheets (say I know I want to go back
to worksheet 5). How can I select that particular sheet
(either in the loop or after I am out of the loop)? I
can't say sheet(5) select, because there may have been
more sheets created before these loops were run.

How can I do something in this to make these sheets
identifiable so I can update them later in the code.

Thanks

Steve
 
After the Sheet.Add put
If aaa= 1 And wksh = 5 Then 'or whenever
strSheetName = Activesheet.Name
End If

then refer to the sheet as
Sheets(strSheetName)

or set a variable, e.g.
Set sheet5 = Activesheet

then use this object
Sheet5.

Kevin Beckham
 
add th esheets to a collection, then you can refer to
them using the relevant key

Example
Sub AddSheets()
Dim clSheets As Collection
Dim ws As Worksheet
Dim index As Long
Set clSheets = New Collection

For index = 1 To 10

Set ws = Worksheets.Add

With ws
.Range("A1").Value = _
"This is sheet index=" & index
End With
clSheets.Add ws, CStr(index)
Next

' now the collection will hold ten sheets
' choose one at random
index = Int(Rnd * 10) + 1
Set ws = clSheets(index)
With ws
.Range("A2").Value = "chosen randomly"
.Activate
End With



End Sub

Patrick Molloy
Microsoft Excel MVP
 
Can you tell me what this part of your code is doing?

<SNIP>

Dim clSheets As Collection
Dim ws As Worksheet
Dim index As Long
Set clSheets = New Collection

<END SNIP>


I don't really know what 'DIM' is asking and what 'Long' is all about.

Linc

Sorry, very new at this
 
Linc,

The 'Dim' statement is used to declare a variable (its name is short for
'Dimension', going back to the earliest days of the BASIC programming
language), and the 'As Long' indicates what type of variable is being
declared. A Long type of variable is 4 bytes in length, and can contain a
whole number between about +/- 4 billion.

So the statement

Dim Index As Long

essentially tells VBA to declare a Long type variable and give it a name
'Index'. Code can then store whole numbers between +/- 4 billion in this
variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top