Selecting multiple sheets

[gaj104]

Hi I'm having a problem with this code I made. I know I need to put a with sheets statement but unsure how do do this.

I'd really appreciate some help.

I'm using

Sub PrintPack()

Dim lRow As Long
Dim sName As String
Dim ws As Worksheet

Set ws = Sheets("Print_Split")

lRow = ws.UsedRange.Rows.Count

For i = 2 To lRow
    
    
    If ws.Range("B" & i).Value = "Y" Then
    
        Sheets(ws.Range("A" & i).Value).Select

    End If
    
Next i


End Sub

It will select the sheet if I have put a Y next to the sheet name.

Thanks

 

[erik.van.geit]

Hi,

your code is working for me
what is the problem ?

kind regards,
Erik

 

[gaj104]

Hi,

It doesn't multi select the sheets, only 1, then moves onto the next.

 

[erik.van.geit]

this works for me

Option Explicit

Sub operate_on_sheets()
'Erik Van Geit

Dim temp As Variant
Dim SheetsArray As Variant
Dim LR As Long
Dim i As Long
Dim j As Long
    
    With Sheets("Print_Split")
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    temp = .Range("A2:B" & LR)
    ReDim SheetsArray(1 To Application.WorksheetFunction.CountIf(.Columns(2), "Y"))
        For i = 1 To UBound(temp)
            If temp(i, 2) = "Y" Then
            j = j + 1
            SheetsArray(j) = temp(i, 1)
            End If
        Next i
    End With

Sheets(SheetsArray).Select

End Sub

 

[gaj104]

Excellent, thanks eric. I was toying with the array type, but I'm not good enough to understand it fully.

Originally i tried sending through a string. Unsurprisingly it failed

Thanks again

 

[erik.van.geit]

if you SPLIT it, you can use a string
but the limits ??
NOT RECOMMENDED

Option Explicit

Sub operate_on_sheets()
'Erik Van Geit

Dim temp As Variant
Dim SheetsArray As String
Dim LR As Long
Dim i As Long
Dim j As Long
    
    With Sheets("Print_Split")
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    temp = .Range("A2:B" & LR)
        For i = 1 To UBound(temp)
            If temp(i, 2) = "Y" Then
            j = j + 1
            SheetsArray = SheetsArray & temp(i, 1) & ","
            End If
        Next i
    End With

SheetsArray = Left(SheetsArray, Len(SheetsArray) - 1)
Sheets(Split(SheetsArray, ",")).Select

End Sub