Sheets(Array("101", "103")).Select
The above is a line of code produced by the Excel macro writer. I need to iterate thru several lines of a worksheet and pick out appropriate items to put into the array statement. . .and then have the code actually run! I can get the items ("101", "107", "13787" etc) into a variable, but when I get to the line of code that says
Sheets(Array(variable)).Select
I get a runtime error. How do I build the command so that it runs??
******
Below is the code that I have so far. There is a list of items on worksheet "INDEX" that I may want to print a report for. This code iterates through that very nicely, but I can't get the command that selects the multiple number of sheets to operate. Thank you for your inquiry.


Sub NewPrAll()
' PrAll Macro
' Macro recorded 9/21/2004 by Henry Sheldon
'
    Sheets("Index").Select
    Range(B11:D50) = PrBlk
    Dim PrSel As Variant
    Dim Flag1, Flag2 As Boolean
    Dim RowNdx, ColNdx As Integer
    Flag1 = True 'True = There is a crane number value in the first column
    Flag2 = False 'False = First time thru
    RowNdx = 1
    ColNdx = 3
    Do While Flag1
        'Check to see if this crane log is to be printed
        If UCase(Range("PrBlk").Cells(RowNdx, ColNdx).Value) = "X" Then
            If Not Flag2 Then
                Flag2 = True
                PrSel = Chr$(34) + Trim$(Str$(Range("PrBlk").Cells(RowNdx, ColNdx - 2).Value)) + Chr$(34)
            Else
                PrSel = PrSel + ", " + Chr$(34) + Trim$(Str$(Range("PrBlk").Cells(RowNdx, ColNdx - 2).Value)) + Chr$(34)
            End If
        Else
            'Check for any more cranes in the list
            If Str$(Range("PrBlk").Cells(RowNdx, ColNdx - 2).Value) = 0 Then
                Flag1 = False
            End If
        End If
        RowNdx = RowNdx + 1
    Loop
' The line below this was created by the Excel Macro Writer.
' Sheets(Array("100", "105", "106", "107", "108", "101", "102", "103", "104", _
        "109", "110", "111", "39254", "112", "113", "114", "115", "116", "117", _
        "118", "12343", "12345", "119")).Select
'I want the line below this one to operate like the line above this one.
    Sheets(Array(PrSel)).Select
    If Len(Sheets("index").Range("c1").Value) > 0 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Else
        mbMsg = MsgBox("Please enter a date value" _
        + vbCrLf + "then run the macro again.", vbOKOnly, "Start Date??")
    End If
    Sheets("Index").Select
    Range("C1").Select
End Sub

Can you be more specific? - eg some of the code you have.

I think this is what you want
-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

Mr. Sheldon,

Did you ever figure out how to get the sheets selected by the array to print? I'm needing to know the code to do the exact same thing; cycle through each worksheet in the workbook, if a cell's value on each worksheet is <> 0 then I need each worksheet's name to be preserved in a Sheets(Array() format and then be able to .Select, go to Print_Area and print only those particular sheets that have a cell value <> 0 to PDF.

I have been searching high and low for the specific code to do the above. I've seen code like the response that was given to you (which by the way defines ahead of time which worksheets to print) but nothing that actually tells me how to write the code to .Select and print the array that was dynamically produced.

Please help if you can.
Thanks.
Theresa Guerra

with regard to the below mail>>>>

how do i select multiple sheets using an index for sheets?
for example.
1. i=6
   sheets(i).select 'this select the 6th sheet in the workbook

like-wise if i want to select sheets from A to B (A & B should be variable integers) what will be the command in VBA.


Thanks

Nuwan

Hello,
I had the same problem with the array function on a different context. Actually I found out the array function just gives back a variant object. Try this, it worked for me:

dim temp as variant, maxelements as integer

'loop through the sheets and dinamically change array size every time
'you want to add a sheet name:

maxelements = maxelements + 1
redim preserve temp(1 to maxelements)
temp(maxelements) = newsheetname 'whatever the name of the sheet you
                                 'want to select is

'then use the temp variant array as an index in the sheets object

sheets(temp).select



Again I don't know if it will work with the sheets object but it worked for me adding values for a scenario (sheets.scenarios.add procedure).

Good Luck,

Santiago