• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro to "only" print sheets where value in SubTotal cell is greater than 0

A

ArbeitWali

New Member
Hi All, I have a workbook with multiple sheets (attached).

When printing, I need help deleting "tab(s)" in array with "0" zero in SubTotal located in a particular cell in the worksheet tab (Usually cell AN65 and so forth). The SubTotal is in a cell in each tab. The idea is to get Excel to only print tabs where we have values equal greater than 0 in the SubTotal cell.

Code currently reads the following:

Sheets(Array("GC's", "DIV 2", "DIV 3", "DIV 4", "DIV 5", "DIV 6", "DIV 7", "DIV 8", "DIV 9", "DIV 10", "DIV 14", "DIV 15", "DIV 16")).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

I tried calling each sheet to check cell value, but no lucks.

But, in the above process, all tabs in the Array get printed which i don't want.
Instead, it should only print tabs or sheets where the value in the SubTotal cell is greater than 0. Any help is appreciated?

Mod Edit: Code from duplicate thread
Code: 
Sub PrintCDivSum()


'error trap added for printing support on 64 and 32 bit OS

On Error Resume Next


Dim ws As Worksheet

Dim ws2 As String

Dim DefPrintName As String


DefPrintName = Application.ActivePrinter


UserForm1.Show


If UserForm1.Cancel = "True" Then

  Unload UserForm1

  Exit Sub

End If


If UserForm1.OK = "True" Then

  If UserForm1.OptionButton2.Value = True Then

  Application.ActivePrinter = "Adobe PDF on Ne02:"

  Else: End If

End If


Unload UserForm1



Application.ScreenUpdating = False


ws2 = ActiveSheet.Name


  With ThisWorkbook

  For Each ws In .Worksheets

  Select Case ws.Name

  Case "GC's"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

   .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

   .PageSetup.PrintArea = .Range("l1a,l2a").Address

  End With

  Case "DIV 2"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l3a,l4a,l5a,l6a,l7a,l8a,l9a,l10a,l11a,l12a,l13a").Address

  End With

  Case "DIV 3"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l14a,l15a,l16a,l17a,l18a").Address

  End With

  Case "DIV 4"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

   .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

   .PageSetup.PrintArea = .Range("l19A").Address

  End With

  Case "DIV 5"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l20a,l21a").Address

  End With

  Case "DIV 6"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l22a,l23a").Address

  End With

  Case "DIV 7"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l24a,l25a,l26a,l27a,l28a").Address

   End With

  Case "DIV 8"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l29a,l30a").Address

  End With

  Case "DIV 9"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

   .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l31a,l32a,l33a,l34a,l35a,l36a").Address

  End With

  Case "DIV 10"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l37a,l38a").Address

  End With

  Case "DIV 14"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

   .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l39A").Address

  End With

  Case "DIV 15"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l40A,l41a,l42a").Address

  End With

  Case "DIV 16"

  With ws

  .PageSetup.PrintArea = "" 'clear any existing print area

  .PageSetup.Orientation = xlPortrait

  .PageSetup.PaperSize = xlPaperLetter

  .PageSetup.Zoom = False

  .PageSetup.FitToPagesWide = 1

  .PageSetup.FitToPagesTall = 1

  .PageSetup.PrintArea = .Range("l43A").Address

  End With

  'Case Else

  End Select

  Next ws

  End With



Sheets(Array("GC's", "DIV 2", "DIV 3", "DIV 4", "DIV 5", "DIV 6", "DIV 7", "DIV 8", "DIV 9", "DIV 10", "DIV 14", "DIV 15", "DIV 16")).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False


'ActiveWindow.SelectedSheets.PrintPreview


Application.ActivePrinter = DefPrintName

Application.ScreenUpdating = True

Sheets(ws2).Select


End Sub
 
Last edited by a moderator:
You will need to have your code check every total line before adding it into the range. Is the total always in AN65, or can it move?
 
No, the total is in tables in many sheets and appears in different but specific cells each time, in each tab.

So its always in columns "AN", and cells AN65, AN65, AN166, AN268, AN370, AN472, AN574.
 
You must log in or register to reply here.
Back
Top