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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
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,037
Office Version
  1. 365
Platform
  1. Windows
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,037
Office Version
  1. 365
Platform
  1. Windows
You would have to set the visible property to TRUE, then Printout and then set the visible property back to FALSE
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top