Selecting Multiple Worksheets Via VBA

[hedrijw]

I've tried to select all sheets and then format all selected without success in VBA.

The recorded macro to select multiple sheets and activate one of them follows:

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

I've been unable to implement this in VBA for any number of worksheets. Can someone help?
This message was edited by hedrijw on 2002-07-10 09:16

 

[Juan Pablo González]

What is "any number" of sheets ? how would the macro know which sheets to select ?

 

[hedrijw]

Where the number of sheets =

activeworkbook.sheets.count

i.e. all sheets in the workbook.

Probably it would be all sheets except the first sheet
This message was edited by hedrijw on 2002-07-10 09:27

 

[Jay Petrulis]

Hi,

Are you trying to group the sheets? If so, after the .Select put (false) and you will group them.

Option Explicit

Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    ws.Select (False)
Next ws
End Sub

Bye,
Jay

 

[NateO]

I think you could just do the following:<pre>
Sub gram_em()
Sheets.Select (False) ' Using Jay's False
End Sub</pre>

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-10 09:42

 

[hedrijw]

Thanks Jay!!!

 

[Jay Petrulis]

On 2002-07-10 09:39, NateO wrote:
I think you could just do the following:<pre>
Sub gram_em()
Sheets.Select (False) ' Using Jay's False
End Sub</pre>

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-10 09:42

Good one! I did not know that.

 

[hedrijw]

What I really wanted to do was select all sheets except the first one, then apply formatting changes to the 2nd sheet so they would be applied to all sheets higher than 2
I ended up with:

for i = ActiveWorkbook.Sheets.Count to 2 step -1
sheets(i).select (False)
next

This ended up with the second sheet being the active sheet.

Thanks All!
This message was edited by hedrijw on 2002-07-10 10:11

 

[NateO]

What happens if the first sheet is active and you run this? I think it'll grab the 1st sheet too?

Something like the following will fire every time:

<pre>
Sub test()
Application.ScreenUpdating = False
Sheets(2).Activate
For i = 2 To Sheets.Count
Sheets(i).Select (False)
Next
End Sub</pre>


Incidentally, you shouldn't have to select the sheet to make this work, e.g.,

<pre>
Sub frmt()
For i = 2 To Sheets.Count
With Sheets(i).[b3]
.Interior.Color = RGB(0, 0, 250)
With .Borders
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 55
End With
End With
Next
End Sub</pre>

If you're formatting programatically....