MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Kevin    Date: Oct 08    Category: MS Office    Views: 532

Please help........

Purpose of below code is to print any worksheet that named "Exec *". Please
advise what I did wrong. Error message is: Run-time error '91' (Object variable
ro with block variable not set).

Sub testing()
Dim WBook As Workbook
Dim WSheet As Worksheet
For i = 1 To WBook.Sheets.Count
If TypeName(WBook.Sheets(i)) = "Exec *" Then WBook.Sheets(i).PrintOut
Next i
End Sub



3 Answers Found

Answer #1    Answered By: Betty Fischer     Answered On: Oct 08

Before you can use an object  variable like Wbook, you have to use the SET
command to point it to a real object. For example:

Set Wbook = ActiveWorkbook

However, I'm not sure you need an object variable  in this case. Excel VBA has
a predefined object called ActiveWorkbook which always refers to the open
workbook which is currently active (to be sure it's the active workbook, click
in any cell in the workbook just before running your macro). Using
ActiveWorkbook the code  might look like this:

Sub testing()
Dim i As Integer
For i% = 1 To ActiveWorkbook.Sheets.Count
If LCase(Left(Sheets(i%).Name, 4)) = "exec" Then
Sheets(i).PrintOut Copies:=1
End If
Next i%
End Sub

Answer #2    Answered By: Mabel Davis     Answered On: Oct 08

Just insert the following:
Set WBook = ActiveWorkbook before the loop.
and then at the end
set WBook = nothing

Answer #3    Answered By: Ernesto Robinson     Answered On: Oct 08

You are using the wrong  property of the worksheet.

If instr(WBook.Sheets(i).Name, "Exec ") Then WBook.Sheets(i).PrintOut

Try this and see if it works.

Didn't find what you were looking for? Find more on Print Or get search suggestion and latest updates.