Selecting all worksheets for creating Macros

  • Thread starter Thread starter rajltd
  • Start date Start date
R

rajltd

Hi,

I am trying to select all worksheets (with names other than Sheet1,
etc.) as part of creating a new macro. But on running the macro on
other excel workbook, I get an error for the name of the worksheet.
Also I get an error if i run the marco for more sheets than the
original macro worksheet.

Thanks for any help
 
One way:

Public Sub SelectAllButSheet1()
Dim colWks As New Collection
Dim wkSht As Worksheet
Dim i As Long
With Workbooks("Book2.xls")
.Activate
For Each wkSht In .Worksheets
If wkSht.Name <> "Sheet1" Then colWks.Add wkSht
Next wkSht
If colWks.Count > 0 Then
colWks(1).Select
For i = 2 To colWks.Count
colWks(i).Select False
Next i
End If
End With
End Sub
 
Back
Top