How to multi-select worksheets?
 

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

How to multi-select worksheets?
 

Hi OKLover,

Try:

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

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

End Sub
'<<=============

---
Regards,
Norman


"OKLover" wrote in message
...
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

How to multi-select worksheets?
 

Try this

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


--

HTH

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


"OKLover" wrote in message
...
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

How to multi-select worksheets?
 

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?



"OKLover" wrote:

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

How to multi-select worksheets?
 

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
'<<============


---
Regards,
Norman


"OKLover" wrote in message
...
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?



"OKLover" wrote:

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

How to multi-select worksheets?
 

Hi OkLover,

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

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

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

---
Regards,
Norman

How to multi-select worksheets?
 

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)


"OKLover" wrote in message
...
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?



"OKLover" wrote:

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

How to multi-select worksheets?
 

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

Norman Jones wrote:
Hi OkLover,


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


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

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

---
Regards,
Norman



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

How to multi-select worksheets?
 

Hi Andy,

Good point!

Thank you.

---
Regards,
Norman



"Andy Pope" wrote in message
...
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

Norman Jones wrote:
Hi OkLover,


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


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

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

---
Regards,
Norman



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info