VBA Print Routine

[wid2001]

I have a workbook with multiple sheets, some of which are hidden. Currently I have a button on a userform with this code for printing

Private Sub cmd_printfltplan_Click()
Dim Printx As String
Dim sCurrentPrinter As String


'Setting the current printer to the active printer
sCurrentPrinter = ActivePrinter


ActivePrinter = sCurrentPrinter
Printx = InputBox("How many copies would you like?")
Sheets(Array("175", "RAW", "LFR")).PrintOut Copies:=Printx 


End Sub

This works for printing the listed sheets but I would like to hide the sheets I am going to print (175, RAW, LFR) so that the user interfaces with my userform and just prints the sheets. Hiding the sheets causes my print routine to fail. I know I have to unhide during the printing process but I have not been able to write the code to make it work. Anyone have any suggestions? I have found multiple routines using screen updating on the forum but haven't been able to make anything fit what I am doing.

Thanks

 

[dchaney]

Try this, it will ask for print count, then it will un-hide the sheets, print them and re-hide them... the user will be none the wiser and will never see anything happen...

There is no need to call out the printer, it should use the default printer for Excel:

Private Sub cmd_printfltplan_Click()

Dim Printx As String

Application.ScreenUpdating = False

Printx = InputBox("How many copies would you like?")
ShtsToHide = Array("175", "RAW", "LFR")

For Each ShtName In ShtsToHide
    Worksheets(ShtName).Visible = True
Next ShtName

    Sheets(Array("175", "RAW", "LFR")).PrintOut Copies:=Printx

For Each ShtName In ShtsToHide
    Worksheets(ShtName).Visible = False
Next ShtName

Application.ScreenUpdating = True

End Sub

 

[wid2001]

That worked great. I had to dim ShtsToHide and shtname as variants but once I did that it worked perfect. Thanks for the help!

 

[MARK858]

I had to dim ShtsToHide and shtname as variants

ShtsToHide should be dimmed as Worksheet and shtname as String.
If you are going to dim them as Variant then you might as well not declare any of the variables (and remove Option Explicit)

 

[wid2001]

Thanks. Still not very efficient with VBA. I'll make those changes. I appreciate the reply.

 

[dchaney]

Glad it worked for ya, sorry forgot to add the Dims on them for ya...