Printing Select Tabs Only

DGB

Board Regular
Joined
Oct 17, 2007
Messages
134
Hi,

I have a Services Quote work book that contains many tabs only some of which may be used for a particular quote.

I would like to have a macro that I could assign to a button that would check the value of a particular field on each tab and if that field is greater than 0, then it should select that tab as one to be printed. Once all tabs are checked and either selected or not selected, the macro should print the selected tabs.

Thank you in advance for you help with this macro.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi DGB,

You haven't said what cell contains the value to be evaluated, so I've written the following to check for it in cell A2 - note that it simply prints each tab where the value in A2 (in my example) is greater then zero instead of trying to select each one and then printing them all at the end (same result though):

Code:
Sub PrintSpecificTabs()

Dim intShtCount As Integer
   
   For intShtCount = 1 To Worksheets.Count
      With Sheets(intShtCount)
        'If the value of cell A2 is greater than zero, then...
        If .Range("A2").Value > 0 Then
            '...print the tab.
           .PrintOut
        End If
      End With
   Next intShtCount

End Sub

HTH

Robert
 
Upvote 0
Robert,

Thank you so much for your reply. I have implemented your code and it works well. I needed to first copy the contents of the cell to be evaluated on each tab to A2 on each tab and it worked like a charm.

Perhaps I could impose on your knowledge a little further.

If I wanted to evalute the cell on each tab in its original location, how would that code be written given that the location on each tab is not the same. Is there a way to name the cell on each tab and have it check for that?

Also, would it be possible to add a line of code such that it would not only print the wanted tabs but would delete the unused tabs. The issue is that the reciepients of the woorkbook are confused by the unused tabs.

Thanks again for your help.

Dan
 
Upvote 0
Hi Dan,

Sorry, but I'm not sure what you mean by If I wanted to evaluate the cell on each tab in its original location, how would that code be written given that the location on each tab is not the same. Is there a way to name the cell on each tab and have it check for that? so if you could provide some more info I'll try and work something out :confused:

Not sure why you'd want to delete tab(s) as it can lead to problems down the track (hiding the tab(s) maybe a better option). That said, the following code will delete those tabs where the value in cell A2 is equal to or less than zero (or is blank) or else print it:

Code:
Sub SheetEvaulation()

    Dim wrkSheet As Worksheet
        
        For Each wrkSheet In Worksheets
        'If the value of cell A2 is greater than zero, then...
        If wrkSheet.Range("A2").Value > 0 Then
            '...print the tab.
            wrkSheet.PrintOut
        'Else...
        Else
            '1. Turn off error messages, _
             2. Delete the tab, and _
             3. Turn on error messages
            Application.DisplayAlerts = False
            wrkSheet.Delete
            Application.DisplayAlerts = True
        End If
    Next wrkSheet

End Sub

HTH

Robert
 
Upvote 0
Hi Robert,

Hiding the tabs or sheets is a much better idea. Thanks

in response to my last question concerning the original location....

The code you gave me evaluates the value of cell A2 on every sheet. In my workbook, the cell that I need to evaluate is not always A2. It may be E34 on one tab and D12 on another etc. How would I code that? I imagine that i could have a separate line of code for each tab so that the cell address could be referenced individually, but I was hoping that there was a way to name every cell to be evaluated with a common name (example "target") and then when evaluating each tab, evaluate "target" rather than "a2" as you have done.

Thanks so much for your help.
 
Upvote 0
Hi Dan,

As named ranges need to be unique this method won't suffice as you'd have to have a separate one for each tab.

Though ideally the layout would have been the same throughout the tabs (especially for the key data) if you can link the key cell in each tab to a consistent blank (unused) cell and then use the code to reference that cell it will do the job.

HTH

Robert
 
Upvote 0
Robert,

I have done as you suggested and it does indeed work nicely.

Thanks again for all your help.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top