Print only the used sheets in all open files

[Bill]

I have the below macro to print the sheets that have info in cells D6 or D3
for all open workbooks. "PrintAllOpenFiles.xlsm" is the file in the
xlstartup dir. If u run the small macro listed first within a single file it
works but inside the bigger macro it doesn't. It doesn't look like anything
happens - no debug option or anything. Any help is appreciated.

'Print Used Sheets
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
If sh.Range("D6").Value <> "" Or sh.Range("D3").Value <>
"" Then
sh.PrintPreview
'sh.PrintOut copies:=1
End If
End If
Next





Sub PrintAllOpenFiles()

Dim x As Workbook

' Loop through all open workbooks.
For Each x In Application.Workbooks

' You don't want to print this workbook.
If x.Name <> "PrintAllOpenFiles.xlsm" Then

' Activate the workbook.
x.Activate

'Print the active worksheet in the current workbook.
'x.SelectedSheets.PrintOut copies:=1


'Print Used Sheets
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
If sh.Range("D6").Value <> "" Or sh.Range("D3").Value <>
"" Then
sh.PrintPreview
'sh.PrintOut copies:=1
End If
End If
Next

' Close the current workbook.
x.Close

'End If
Next x


'Remove the apostrophe from the next line of code if you
'want to exit Excel when this process has completed.
'Application.Quit

End Sub

 

[Dave Peterson]

You're only looking at the workbook with the code with this line:
For Each sh In ThisWorkbook.Worksheets

You could use:

Dim sh as worksheet
dim wkbk as workbook

For each wkbk in application.workbooks
if wkbk.name = thisworkbook.name then
'skip it
else
for each sh in wkbk.worksheets
if sh.range("d3").value <> "" _
or sh.range("D6").value <> "" then
sh.printout copies:=1, preview:=true 'another way to preview!
end if
next sh
'really close that workbook?
wkbk.close savechanges:=false 'true????
end if
next wkbk

===
Untested, uncompiled. Watch for typos.

 

[Bill]

Thanks Dave - works great.

You're only looking at the workbook with the code with this line:
For Each sh In ThisWorkbook.Worksheets

You could use:

Dim sh as worksheet
dim wkbk as workbook

For each wkbk in application.workbooks
if wkbk.name = thisworkbook.name then
'skip it
else
for each sh in wkbk.worksheets
if sh.range("d3").value <> "" _
or sh.range("D6").value <> "" then
sh.printout copies:=1, preview:=true 'another way to preview!
end if
next sh
'really close that workbook?
wkbk.close savechanges:=false 'true????
end if
next wkbk

===
Untested, uncompiled. Watch for typos.