Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another loop & sheet question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another loop & sheet question
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another loop & sheet question
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 -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another loop & sheet question
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another loop & sheet question
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 "Linc" wrote in message ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop question | Excel Discussion (Misc queries) | |||
Loop question | Excel Discussion (Misc queries) | |||
Loop QUESTION to end of data range... | Excel Worksheet Functions | |||
Loop QUESTION to end of data range... | Excel Worksheet Functions | |||
Password Loop question. | Excel Discussion (Misc queries) |