VBA to print only select sheets based on a cell value

[babyg]

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.

 

[Momentman]

This way so you can see how to structure it

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

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]

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]

Something like this

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]

That works perfect! Thank you.

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

 

[Momentman]

You would have to set the visible property to TRUE, then Printout and then set the visible property back to FALSE