Not Print if Cell A1 in each of these sheets = 0

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
I have the following code:

Code:
    Sheets("TITLEPAGE").Visible = True
    Sheets("TITLEPAGE").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("TITLEPAGE").Select
    ActiveWindow.SelectedSheets.Visible = False
    Application.Run "Assets"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "LiabEq"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "IncomeStmt"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Intercompany"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Inventory"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "PPE-MONTH"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Intangibles"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Headcount"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "debt"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Bonus"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Statistics"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"
    Application.Run "Check"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Run "DataPage"

How can I make the macro Not Print the page it selects in this code, if on that specific page it selects, if cell A1 (on that page) = 0, meaning there is no data on the page.

ANy help?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
if Sheets("TITLEPAGE").range("A1").value <>0 then
'print code here
else
msgbox("No data to print!")
end if
or if the sheet changes

Code:
if Sheets(activesheet.name).range("A1").value <>0 then
'print code here
else
msgbox("No data to print!")
end if
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Here's one way with your exisiting code:

Code:
    Sheets("TITLEPAGE").Visible = True
    Sheets("TITLEPAGE").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWindow.SelectedSheets.Visible = False
    Application.Run "Assets"

    If Not Range("a1") = 0 Then
        If Not Range("a1") = vbNullString Then
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        End If
    End If

Not sure what your application.run "xxxx" is doing, but if it's just selecting the sheets then this could all be much shorter with a FOR statment assuming you're operating on all the sheets in the workbook.
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Will that msgbox stop the printing if there is no data? I need it to cycle through all the sheets. For example, if sheet 3 out of 15 has no data, it doesn't print sheet 3, but it continues to sheet 4, sheet 5, etc.
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501

ADVERTISEMENT

You are correct. Application.run "xxxx" is just selecting the sheets. I have buttons for the user, to press to "go to" that specific sheet.
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
gottimd said:
How would the "For" statement work?


Code:
Sub printall()
    Dim ws As Worksheet

    Sheets("TITLEPAGE").Visible = True
    For Each ws In ThisWorkbook.Worksheets
        If Not Range("a1") = 0 Then
            If Not Range("a1") = vbNullString Then
                ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            End If
        End If
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top