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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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
 

Forum statistics

Threads
1,141,427
Messages
5,706,391
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top