Printing Pages based on cell contents Query

[5h1v]

Hi,

I have a worksheet that is set up to 8 print areas I think I have made a macro that should only print each page if the first cell in it has contents but I cant seem to get it to work, would you be so kind as to have a look please, apologies if it's wrong I'm pretty new to VBA.

Sub Printing()
'
' Printing Macro
'
  If IsEmpty(Range("b2").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If
   If IsEmpty(Range("b52").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1
End If
    If IsEmpty(Range("b102").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1
End If
  If IsEmpty(Range("b152").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=4, Copies:=1
End If
    If IsEmpty(Range("b202").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=5, Copies:=1
End If
    If IsEmpty(Range("b252").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=6, Copies:=1
End If
  If IsEmpty(Range("b302").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=7, Copies:=1
End If
  If IsEmpty(Range("b352").Value) = True Then
        Cancel = True
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=8, Copies:=1
End If
End Sub

Thanks for the help

 

[jimbo_612]

Hi, i'm also new in VBA
Your script looked like a nice base, so i worked on it and i made it work for my case and it looks to me that you have same problem


chages that i made are:


- I added ELSE to your IF statement
- range of pages in every IF statement



Sub Printing()
'
' Printing Macro
'
If IsEmpty(Range("G11").Value) = True Then
Cancel = True
Else
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If
If IsEmpty(Range("G40").Value) = True Then
Cancel = True
Else
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1
End If
If IsEmpty(Range("G69").Value) = True Then
Cancel = True
Else
ActiveWindow.SelectedSheets.PrintOut From:=3, To:=3, Copies:=1
End If


End Sub




i have three pages on a worksheet and based on value of the cells (G11=1; G40=null; G69=1) i get printed out pages 1 and 3.


hope it helps


Also found another one that works, but this one is better for me because i can check sheet even if it is not active. You can speify sheet in the 4th line (name of my sheet = print)
Set wkspodinvoice = ThisWorkbook.Worksheets("print")


Option Explicit
Sub Print_Parameters()


Dim wkspodinvoice As Worksheet
Set wkspodinvoice = ThisWorkbook.Worksheets("print")


With wkspodinvoice
If .Range("G11").Value > 0 Then .PrintOut From:=1, To:=1, Copies:=1
If .Range("G40").Value > 0 Then .PrintOut From:=2, To:=2, Copies:=1
If .Range("G69").Value > 0 Then .PrintOut From:=3, To:=3, Copies:=1
End With


End Sub