Help Selecting Mulitiple Sheets using VBA

[lleifsen]

Hello,

I am trying to select multiple sheets using the following Code:

"Sub CopySheets()
Dim x As Integer
Dim y As Integer
Dim ws As Worksheet
x = ActiveWorkbook.Sheets.Count - 2
y = ActiveSheet.Index
n = Worksheets("Test").Cells(4, 2)
m = Cells(7, 18)
Namex = ActiveSheet.Name
Do Until x = y
If m = n Then
Sheets(Namex).Select False

End If
ActiveSheet.Next.Select
Namex = ActiveSheet.Name
m = Cells(7, 18)
Loop
End Sub"

My problem is...the code will not select more than one sheet at a time. How do I get it to select the first sheet that meets the requirement and then select the other sheets that meet the requirement without unselecting the first sheet?

Thanks,

Lee

 

[Tom Urtis]

Wow a lot of variables in there, with m not being declared btw.

Is it correct to say that you want to select every sheet from and inclucing the active sheet to whichever sheet is the index number that matches the number in cell B4 of the Test sheet? If it smore complicated than that, please explain the logic in words instead of code as to why which sheets should be selected.

 

[Greg Truby]

You need to build an array of the names you need and then select them all at once:

Sub SelectSomeWorksheets()
    
    Let v = Array("Sheet2", "Sheet4")
    
    Worksheets(v).Select
    
    Dim s(2) As String
    Let s(0) = "Sheet1"
    Let s(1) = "Sheet3"
    Let s(2) = "Sheet5"
    
    Worksheets(s).Select
    
End Sub

 

[lleifsen]

Tom,

There are a lot of variables...I am working on that.

No that is not correct.

the book contains several different sheets that get seperated out to certain individuals. for example, b4 = Bill...it is the name of the individual whoes sheets I want to Select and export. However, Bill sheets are not in any given order and depending on the report, the sheets he will get are different. I have a data base to sort all of that out. I am trying to loop through the book and find the Sheets that contain Bill's name in Cells(7 , 18) and select only those Sheets.

I guess it would be like using the "Ctrl" key to select non-adjacent sheets.

Thanks for your help,

Lee

 

[Greg Truby]

Lee, all you need to do is run a loop and your tests and build up an array like I showed you and then do the select after the array is built.

 

[Biz]

Try code below

Option Explicit
Option Base 1
Sub SelectSomeWorksheets()
    Dim SheetName
    Dim I As Long
    SheetName = Array("Sheet2", "Sheet4")
    
    For I = LBound(SheetName) To UBound(SheetName)
        Sheets(SheetName).Select
    Next I
End Sub

 

[Greg Truby]

Option Explicit
Option Base 1
Sub SelectSomeWorksheets()
    Dim SheetName
    Dim I As Long
    SheetName = Array("Sheet2", "Sheet4")
 
    For I = LBound(SheetName) To UBound(SheetName)
        Sheets(SheetName).Select
    Next I
End Sub

Biz - (a) why the Option Base 1?

(b) Your loop is extraneous. You should have done either:

Sub SelectSomeWorksheets()
    Dim SheetName
    Dim I As Long
    SheetName = Array("Sheet2", "Sheet4")
 
    For I = LBound(SheetName) To UBound(SheetName)
        Sheets(SheetName(I)).Select
    Next I
End Sub

(the above will not select multiple sheets simultaneously, but at least the loop is now doing something.) Or else you should have done (which is what I did in the code I posted above):

Sub SelectSomeWorksheets()
 
    Dim SheetName
 
    SheetName = Array("Sheet2", "Sheet4")
 
    Sheets(SheetName).Select
 
End Sub

 

[Biz]

Hi Greg,

Thanks for shorter code. No looping too.

Sub SelectSomeWorksheets()
 
    Dim SheetName
 
    SheetName = Array("Sheet2", "Sheet4")
 
    Sheets(SheetName).Select
 
End Sub

Biz