Print VBA

[kyleboyd]

I am trying to create a macro that will print a sheet based on a named cell in the sheet's contents. I have this:

Sub PrintSheets()
Dim Sh As Worksheet
Dim Printed
For Each Sh In ThisWorkbook.Worksheets
If Sh.CodeName <> "Sample" Then
If Sh.Range("Name") <> "" Then
Sh.PrintOut copies:=1
End If
End If
Next Sh
End Sub


Name is the named cell on each sheet.

But it returns this error:
method range of object _worksheet failed

I am a complete newbie to macro's, TIA!

 

[VoG]

Maybe this?

Sub PrintSheets()
Dim Sh As Worksheet
For Each Sh In ActiveWindow.SelectedSheets
    If Sh.Name <> "Sample" Then
        Sh.PrintOut
    End If
Next Sh
End Sub

 

[kyleboyd]

Hi Peter!
Perhaps I was too vague...I think what yours does is just print the active sheet.

What I am trying to do is this:

I have about 40 some odd sheets each with a cell named "Name"

I want to create a Macro that will print the sheet if the Name cell is not blank. Actually come to think of it, I would like it to print if the Name cell is greater than today's date (the Name cell is a Date). Is that clearer?

 

[VoG]

Ah, gotcha. Try

Sub PrintSheets()
Dim Sh As Worksheet
For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Range("Name").Value > Date Then Sh.PrintOut
Next Sh
End Sub

 

[kyleboyd]

Great! I added

If Sh.Range("Name").Value > Date And Sh.Range("Name")<>"" Then Sh.PrintOut

to account for some cells that are blank (it was giving me an error with the blanks before)

Thanks for the help!