How to multi-select worksheets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, All

I have multiple worksheets, when i write below VBA code, it's ok:

Sheets(Array("Sheet1", "Sheet2",....)).Select

But when i assign it to a variable:

Sub Macro1()
Dim str As String
str = "Sheet1" & Chr(44) & "Sheet2"
Sheets(Array(str)).Select
End Sub

It seems doesn't work, what i can do?


Thanks
 
Hi OKLover,

Try:

'=============>>
Public Sub Macro1()
Dim arr As Variant

arr = Array("Sheet1", "Sheet2")
Sheets(arr).Select

End Sub
'<<=============
 
Try this

Dim str As Variant
str = Array("Sheet1", "Sheet2")
Sheets(str).Select


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks, Norman and Bob

But what i need is loop current workbook to filter and select some worksheets.

ex:

I have five worksheets: sheet1, sheet2, sheet_aa, sheet_ba, sheet_ca

For Each tSheet In thisworkbook.worksheets
If Instr(tSheet.Name, "a") Then
'** Select all of the worksheet which name include the 'a'
End If
Next

What i can do?
 
Hi OKLover,

So select all the sheets whose names include the letter 'a2, try:

'============>>
Sub ATester01()
Dim Sh As Worksheet

For Each Sh In ActiveWorkbook.Worksheets
If InStr(1, Sh.Name, "a") Then
Sh.Select False
End If
Next Sh
End Sub
'<<============
 
Hi OkLover,
So select all the sheets whose names include the letter 'a2, try:
Click to expand...

includes a typo. It should, of course, read:

So select all the sheets whose names include the letter 'a', try:
 
Dim arySheets As Variant
Dim sh As Worksheet
Dim i As Long
ReDim arySheets(0 To 0)
For Each sh In ActiveWorkbook.Worksheets
If LCase(sh.Name) Like "*a*" Then
ReDim arySheets(0 To i)
arySheets(i) = sh.Name
i = i + 1
End If
Next sh
Sheets(arySheets).Select


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Hi Norman,

Just a little tweak to your code so that the activesheet is not included
in the grouping unless it also includes 'a' in tab name.

Sub ATester01()
Dim Sh As Worksheet
Dim blnReplace As Boolean

blnReplace = True
For Each Sh In ActiveWorkbook.Worksheets
If InStr(1, Sh.Name, "a") Then
Sh.Select blnReplace
blnReplace = False
End If
Next Sh
End Sub

Cheers
Andy
 
Back
Top