Selecting Multiple Sheets

D

Dolphinv4

Hi,

(I know I've sent a similar post b4. But i tried very
hard to find dat post and don't know why I can't find it.
Tried searching for all posts posted by me in all threads)

I need a macro such that it'll (1) print the whole
workbook, after that (2) select the first sheet up till
the sheet called "Acc". (I can't put the name of the
sheets coz they change all the time). (3) After selecting
those sheets, the macro will change the font of cells
(a1:a2) to red and then print the whole workbook again
(including those not selected in (2)). After that, (4)
the macro will select those sheets and change those cells
back to white font.

I tried to start with a simple macro as shown below. Not
only does it not work properly (the font does not change
back to white, there's an error 400).

Sub Print_Invs()

ThisWorkbook.PrintOut
Sheets().Select
Range("a1:a2").Select
Selection.Font.ColorIndex = 3
ThisWorkbook.PrintOut
Sheets().Select
Range("a1:12").Select
Selection.Font.ColorIndex = 2


End Sub


Can someone please help me out?

Thanks & sorry for posting another thread.

Regards,
Val
 
D

Dave Peterson

First, I use Google to find old posts:

If you're looking for old posts, you can use google (maybe a few hours behind)
to search for stuff you've posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

and one way that seemed to work for me:

Option Explicit
Sub testme01()

Dim testWks As Worksheet
Dim wks As Worksheet
Dim myAddr As String
Dim DoneWithGroup As Boolean

myAddr = "A1:A2"

With ActiveWorkbook
Set testWks = Nothing
On Error Resume Next
Set testWks = .Worksheets("acc")
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "no ACC worksheet!"
Exit Sub
End If

.PrintOut preview:=True

DoneWithGroup = False
For Each wks In .Worksheets
If wks.Name = testWks.Name Then
DoneWithGroup = True
End If
wks.Range(myAddr).Font.ColorIndex = 3
If DoneWithGroup Then
Exit For
End If
Next wks

.PrintOut preview:=True

DoneWithGroup = False
For Each wks In .Worksheets
If wks.Name = testWks.Name Then
DoneWithGroup = True
End If
wks.Range(myAddr).Font.ColorIndex = 2
If DoneWithGroup Then
Exit For
End If
Next wks
End With

End Sub

(I kept the preview:=true to save some trees.)
 
Top