PDA

View Full Version : [SOLVED] Select Sheets Array If



FeatherM
04-03-2019, 03:55 AM
Hi,

I want to write a code to select multiple sheets in my workbook but only if condition is met for most of them.
Example, I want that VBA selects first 3 sheets and for the rest of the 29 sheets only if cell value in I1 = YES; if value = NO, then not to select that sheet.

ThisWorkbook.Sheets(Array(MyArray)).Select
HOW TO DEFINE MyArray?
Sheets("CoverPage").Select
Sheets("MRAge").Select
Sheets("MROverall").Select

Sheets("MRD1").Activate
Dim strName As String
strName = "I1"
If strName = "YES" Then
Sheets("MRD1").Select
End If

Sheets MR I have from 1 to 29 and YES/NO should be applied for them.

大灰狼1976
04-03-2019, 04:39 AM
Hi FeatherM!
Welcome to vbax forum.
No tested

sub test()
dim arr, n&, sh&
arr=array("CoverPage", "MRAge", "MROverall")
n=2
for sh=4 to sheets.count
with sheets(sh)
if .[i1] = "YES" then
n=n+1
redim preserve arr(n)
arr(n)=.name
end if
end with
next sh
ThisWorkbook.Sheets(arr).Select
end sub

p45cal
04-03-2019, 10:51 AM
or:
Sub blah()
Sheets(1).Select
For i = 2 To Sheets.Count
If UCase(Sheets(i).Range("I1").Value) = "YES" Or i < 4 Then Sheets(i).Select False
Next i
End Sub

FeatherM
04-04-2019, 01:07 AM
Hi,
Tried your code and run perfect! Thanks!

FeatherM
04-04-2019, 01:08 AM
Hi 大灰狼1976,
Your code run perfect! Thanks!

FeatherM
05-13-2021, 10:34 PM
Hi,

Need help on Redim Preserve arr(n).
I am running practically the same in new workbook, but in this case the Sheet name is numbers, as 103, etc.
And for some reason the code doesnt work any longer....

Any advice?

For example selection separately works:

For Each List3 In wsCE1.Range("F10:F" & LastrowAct) 'works selection
If List3.Value <> 0 Then
Sheets(CStr(List3.Value)).Select
End If
Next List3

But not in the code:

For sh = 8 To Sheets.Count
With Sheets(sh)
For Each List3 In wsCE1.Range("F10:F" & LastrowAct)
If List3.Value <> 0 Then
'Sheets(CStr(List3.Value)).Select
n = n + 1
ReDim Preserve arr(n)
End If
Next List3
End With
Next sh

SamT
05-14-2021, 08:28 AM
For sh = 8 To Sheets.Count
With Sheets(sh)
For Each List3 In wsCE1.Range("F10:F" & LastrowAct)
If List3.Value <> 0 Then
'Sheets(CStr(List3.Value)).Select
n = n + 1
ReDim Preserve arr(n)
End If
Next List3
End With
Next sh
That makes no sense. That code says:

For Certain Sheets
For each Item on a particular sheet
'
' do stuff to an Array
'
Next Item
Next Sheet

snb
05-14-2021, 09:23 AM
Sub M_snb()
For j = 1 To Sheets.Count
If j < 4 Or Sheets(j).Cells(1) = "a" Then c00 = c00 & "|" & Sheets(j).Name
Next

Sheets(Split(Mid(c00, 2), "|")).Select
End Sub