Dinamic array ?!?!

fcarboni · January 9th, 2013, 01:09 PM

Hi, I'm Fabrizio newbye into VBA and English.... sorry in advance for any error..
I've to format some sheet in my Workbook, the sheet can be into 100 to 350.
for all pages the some formatting.
I think to use a VBA code.
I try with register macro, to select all pages -2 (the db page and the pivot page)
This is the vba code created:

Code:

Sheets(Array("3", "4", "6", "8", "10", "12", "14", "16", "18", "20", "22", "24", "26", "28", _        
"30", "32", "34", "36", "38", "40", "42", "44", "46", "48", "50")).Select    
Sheets("3").Activate    
Sheets(Array("52", "54", "56", "58", "60", "63", "64", "66", "68", "70", "72", "74", "76", _        
"78", "80", "82", "84", "86", "88", "90", "92", "94", "96", "99", "100")).Select Replace _        
:=False    
Sheets(Array("102", "104", "106", "108", "111", "112", "114", "116", "118", "120", "122", _        
"124", "126", "128", "130", "132", "134", "136", "138", "140", "142", "144", "146", "148", _        
"150")).Select Replace:=False    
Sheets(Array("152", "154", "156", "158", "160", "162", "164", "166", "168", "170", "172", _        
"174", "176", "178", "180", "183", "184", "186", "188", "190", "192", "194", "196", "198", _        
"200")).Select Replace:=False    
Sheets(Array("202", "204", "206", "208", "210", "212", "214", "216", "218", "220", "223", _        
"224", "226", "228", "230", "232", "234", "236", "238", "240", "242", "244", "246", "248", _        
"250")).Select Replace:=False    
Sheets(Array("252", "254", "256", "258", "260", "262", "264", "266", "268", "271", "272", _        
"275", "277", "280", "281", "284", "285", "288", "289", "292", "294", "299", "301", "303", _        
"306")).Select Replace:=False    
Sheets(Array("307", "310", "311", "314", "315", "324", "326")).Select Replace:=  False

the max sheets into array are 25 it's a limit?!?!?
then
the number of sheet are variant and with this code I count/select all pages:

Code:

    With ThisWorkbook.Windows(1)
        nFogli = Sheets.Count - 1
        x = 0
        ReDim NomeFoglio(nFogli)
        For Each Foglio In Sheets
             NomeFoglio(x) = Foglio.Name
             x = x + 1
        Next
        For y = 0 To nFogli
            If IsNumeric(NomeFoglio(y)) Then ' for don't select db & pivot sheet
                Worksheets(NomeFoglio(y)).Select
            End If
        Next
    End With

Now can someone help me to merge this 2 part of code??
I've ideas what i want but I can't able to do.
thanks
Fabrizio

fcarboni · January 10th, 2013, 11:13 AM

hi,
the problem solved.
Array isn't necessary,
I can select all pages to format in the same time:

Code:

Sub FormatAll()
For i = 1 To Sheets.Count    
If Sheets(i).Name <> "pivot" And Sheets(i).Name <> "db" Then        
Sheets(i).Select Replace:=False    
End If
Next
...format
End Sub

ty