Match Criteria then not a Print Page

appu_gusai · Sep 24, 2015

hi all

i want vba code for printing a page or not printing a page. Match with multiple criteria in column D in Sheet1, like (Vacant (GP: 6600), Vacant (GP: 5400), Vacant (GP: 4600), Vacant (GP: 4400), Vacant (GP: 4200), Vacant (GP: 2800), Vacant (GP: 2400), Vacant (GP: 1900), Vacant (GP: 1650), Vacant (GP: 1400)) if it match with cell value in column D, page should not be printed. otherwise page should be printed.

thank's in advance

Deepak · Sep 24, 2015

Hi,

Pls clarify the page, either it is sheet or have to match the string in each page of that sheet.

appu_gusai · Sep 24, 2015

in sheet1 total 19 pages and match 19 strings in sheet 1 in column D

in sheet2 total 69 pages and match 69 strings in sheet 1 in column D

thank's

Deepak · Sep 25, 2015

Interesting! let me arrange something!

Deepak · Sep 25, 2015

Hi,

Check this..

Code:

Sub print_the_page()
' Sheet1 is the sheet name where need to check & print
' P1 is the first cell of the range where criteria availabe to check in col D

    dynamic_print_page_address "Sheet1", "P1"
    dynamic_print_page_address "Sheet2", "P1"
   
End Sub
Sub dynamic_print_page_address(ByVal myWS As String, crng As String)
Dim r As Long, c As Long, h As Long, v As Long, hpage As Long, vpage As Long
Dim sr As Long, sc As Long, rng As Range, strEval As String, crange As Range

With Sheets(myWS)
hpage = .HPageBreaks.Count: vpage = .VPageBreaks.Count
sc = 1: sr = 1

For v = 1 To vpage + 1
    For h = 1 To hpage + 1
   
        If v = vpage + 1 Then
            c = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        Else
            c = .VPageBreaks(v).Location.Column - 1
        End If
            If v > 1 Then sc = .VPageBreaks(v - 1).Location.Column
   
        If h = hpage + 1 Then
            r = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
        Else
            r = .HPageBreaks(h).Location.Row - 1
        End If
            If h > 1 Then sr = .HPageBreaks(h - 1).Location.Row
           
    Set rng = .Range(.Cells(sr, sc), .Cells(r, c))
'  4 used for forth col of each page
    strEval = "=sum(COUNTIF(" & Range(crng).CurrentRegion.Resize(, 1).Address & "," & rng.Columns(4).Address & "))"

    If Evaluate(strEval) = 0 Then rng.PrintOut , , 1
   
  Next
  sc = 1: sr = 1
Next
End With

End Sub

appu_gusai · Sep 26, 2015

hi

i cant under stand what is P1?

Deepak · Sep 26, 2015

P1 is the starting range where so called 69/19 mating string exits.

Like as ..

P1,P2,P3,P4,P5........P69
or
P1,P2,P3,P4,P5........P19

appu_gusai · Sep 28, 2015

after 20 pages prints getting error on line 30

Sub print_the_page()
' Sheet1 is the sheet name where need to check & print
' P1 is the first cell of the range where criteria availabe to check in col D

dynamic_print_page_address "GO PAYSLIP", "P1,P2,P4,P5,P6,P7,P8,P9,P10,P11,P12,P16,P17,P18"
dynamic_print_page_address "NGO PAYSLIP", "P2,P3,P4,P5,P6,P9,P10,P11,P19,P20,P22,P24,P25,P26,P27,P28,P29,P30,P38,P39,P40,P41,P42,P43,P46,P47,P49,P50,P53,P54,P55,P56,P58,P59,P60,P61,P62,P63,P64,P65,P66,P67,P68,P69"

End Sub
Sub dynamic_print_page_address(ByVal myWS As String, crng As String)
Dim r As Long, c As Long, h As Long, v As Long, hpage As Long, vpage As Long
Dim sr As Long, sc As Long, rng As Range, strEval As String, crange As Range

With Sheets(myWS)
hpage = .HPageBreaks.Count: vpage = .VPageBreaks.Count
sc = 1: sr = 1

For v = 1 To vpage + 1
For h = 1 To hpage + 1

If v = vpage + 1 Then
c = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
Else
c = .VPageBreaks(v).Location.Column - 1
End If
If v > 1 Then sc = .VPageBreaks(v - 1).Location.Column

If h = hpage + 1 Then
r = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
Else
r = .HPageBreaks(h).Location.Row - 1
End If
If h > 1 Then sr = .HPageBreaks(h - 1).Location.Row

Set rng = .Range(.Cells(sr, sc), .Cells(r, c))
' 4 used for forth col of each page
strEval = "=sum(COUNTIF(" & Range(crng).CurrentRegion.Resize(, 1).Address & "," & rng.Columns(4).Address & "))"

If Evaluate(strEval) = 0 Then rng.PrintOut , , 1

Next
sc = 1: sr = 1
Next
End With

End Sub

Deepak · Sep 28, 2015

You have wrongly written the syntax.

It needs to written like as below.

Code:

Sub print_the_page()
    dynamic_print_page_address "GO PAYSLIP", "P1"
    dynamic_print_page_address "NGO PAYSLIP", "P2"  
End Sub

I have used Range(crng).CurrentRegion.Resize(, 1).Address so it will
auto make a range from P1.

You just need to write most upper cell of criteria range.

Deepak · Sep 28, 2015

I have did some changes, Now use below...

Code:

Option Explicit

Sub print_the_page()
    dynamic_print_page_address "GO PAYSLIP", "P1:P16"
        dynamic_print_page_address "NGO PAYSLIP", "P2:P69"
End Sub
Sub dynamic_print_page_address(ByVal myWS As Worksheet, crng As String)
Dim r As Long, c As Long, h As Long, v As Long, hpage As Long, vpage As Long
Dim sr As Long, sc As Long, rng As Range, strEval As String, crange As Range


With Sheets(myWS)
.Activate
hpage = .HPageBreaks.Count: vpage = .VPageBreaks.Count
sc = 1: sr = 1

For v = 1 To vpage + 1
    For h = 1 To hpage + 1
   
        If v = vpage + 1 Then
            c = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        Else
            c = .VPageBreaks(v).Location.Column - 1
        End If
            If v > 1 Then sc = .VPageBreaks(v - 1).Location.Column
   
        If h = hpage + 1 Then
            r = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
        Else
            r = .HPageBreaks(h).Location.Row - 1
        End If
            If h > 1 Then sr = .HPageBreaks(h - 1).Location.Row
           
    Set rng = .Range(.Cells(sr, sc), .Cells(r, c))
'  4 used for forth col of each page
   strEval = "=sum(COUNTIF(" & .Range(crng).Address & "," & rng.Columns(4).Address & "))"
   
    If Evaluate(strEval) = 0 Then rng.PrintOut , , 1
   
  Next
  sc = 1: sr = 1
Next
End With

End Sub

appu_gusai · Oct 2, 2015

Compile error:

Type mismatch

Option Explicit

Sub print_the_page()
dynamic_print_page_address "GO PAYSLIP", "P1

16"
dynamic_print_page_address "NGO PAYSLIP", "P2

69"
End Sub

Code:

Sub dynamic_print_page_address(ByVal myWS As Worksheet, crng As String)
Dim r As Long, c As Long, h As Long, v As Long, hpage As Long, vpage As Long
Dim sr As Long, sc As Long, rng As Range, strEval As String, crange As Range


With Sheets(myWS)
.Activate
hpage = .HPageBreaks.Count: vpage = .VPageBreaks.Count
sc = 1: sr = 1

For v = 1 To vpage + 1
    For h = 1 To hpage + 1
  
        If v = vpage + 1 Then
            c = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        Else
            c = .VPageBreaks(v).Location.Column - 1
        End If
            If v > 1 Then sc = .VPageBreaks(v - 1).Location.Column
  
        If h = hpage + 1 Then
            r = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
        Else
            r = .HPageBreaks(h).Location.Row - 1
        End If
            If h > 1 Then sr = .HPageBreaks(h - 1).Location.Row
          
    Set rng = .Range(.Cells(sr, sc), .Cells(r, c))
'  4 used for forth col of each page
  strEval = "=sum(COUNTIF(" & .Range(crng).Address & "," & rng.Columns(4).Address & "))"
  
    If Evaluate(strEval) = 0 Then rng.PrintOut , , 1
  
  Next
  sc = 1: sr = 1
Next
End With

End Sub

Deepak · Oct 2, 2015

Change this line.

Sub dynamic_print_page_address(ByVal myWS As String, crng As String)

appu_gusai · Oct 2, 2015

BRO

same above after 20 pages prints getting error on line 30

thank's for this

Deepak · Oct 2, 2015

Share the sample file.

appu_gusai · Oct 2, 2015

pleas find the attachment...................

Deepak · Oct 2, 2015

Where is the criteria to match??

appu_gusai · Oct 2, 2015

On column D on both sheets

Deepak · Oct 2, 2015

What to match on col D??

appu_gusai · Oct 2, 2015

Match "Name" in Coloum D, print a page, not match not print a page

thank's

Deepak · Oct 2, 2015

You have to put somewhere the list of names which needs to be match!

appu_gusai · Oct 3, 2015

yes in another sheet name "VLD" i have same name with this sheet, but in VLD sheet i have name in column C and designation in column F

please find the same attachment here............

Match Criteria then not a Print Page

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Attachments