Print only the used sheets in all open files
 

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

Print only the used sheets in all open files
 

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 wrote:

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

Print only the used sheets in all open files
 

Thanks Dave - works great.

"Dave Peterson" wrote:

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 wrote:

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
.