VBA to print only select sheets based on a cell value

babyg

New Member
Joined
Feb 27, 2009
Messages
24
The main sheet of my workbook has 2 list boxes which control the graphs on the other sheets. I have some vba coding that shows/hides sheets based on the values selected. In the background I have a cell that shows either a 0,1, or 2 based on the selection. Is there some code I can write to print Sheet1 if cell A21 = 0, Sheet2 if cell A21 = 1, and Sheet3 if cell A21 = 2? Any help is appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
This way so you can see how to structure it
Code:
Sub printsheet()
    Dim ws As Worksheet, wks As Worksheet
    Set wks = Worksheets("Main")
    
    Select Case wks.Range("A21").Value
        Case 0
            Sheet1.PrintOut
        Case 1
            Sheet2.PrintOut
        Case 2
            Sheet3.PrintOut
    End Select
    
End Sub

SInce 0-sheet1, 1-Sheet2, 2-Sheet3,Something like this should work
Code:
Sub newprintpout()
    Dim wks As Worksheet
    Set wks = Worksheets("Main")
    Worksheets("Sheet" & wks.Range("A21").Value + 1).PrintOut
End Sub

The code assumes that the name of the main sheet is MAIN, change as necessary.
 

babyg

New Member
Joined
Feb 27, 2009
Messages
24
Thanks so much for the quick repsonse. I was trying to use the first code you posted, but can't seem to get it to work. For simplicity in typing I call the sheets, "Sheet1" etc. I have actually named all the sheets and they are not in sequential order. How can I use the code provided when the sheet names have spaces in them? I tried putting quotes around them, but that didn't work.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Something like this
Code:
Sub printsheet()
    Dim ws As Worksheet, wks As Worksheet
    Set wks = Worksheets("Main")
    
    Select Case wks.Range("A21").Value
        Case 0
            Worksheets("My Sheet1").PrintOut
        Case 1
            Worksheets("New Sheet2").PrintOut
        Case 2
            Worksheets("my sheet3").PrintOut
    End Select
    
End Sub
 

babyg

New Member
Joined
Feb 27, 2009
Messages
24
That works perfect! Thank you.

Is there a way to also make it work on hidden sheets?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You would have to set the visible property to TRUE, then Printout and then set the visible property back to FALSE
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,646
Members
430,150
Latest member
amitk1

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
Top