• 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.

help getting VBA not to print "Ranges" in worksheets if "Total" in given table range is = 0

Status
Not open for further replies.
A

ArbeitWali

New Member
Hey Guys/Ladies,
My code currently selects a set number of Named Ranges to print.. It prints everything. Even zero totals..aaargh.

I need help getting VBA code not to print "Ranges" in worksheets where the "Total" any given table is "0 ".


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
 
Status
Not open for further replies.
Back
Top