Selecting multiple sheets

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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

Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
Hi,

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
this works for me
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
864
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 :oops:

Thanks again
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
if you SPLIT it, you can use a string
but the limits ??
NOT RECOMMENDED
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,799
Messages
5,542,574
Members
410,560
Latest member
1ndependent
Top