Button to print Cell Range for all worksheets whos sheet name begins with "QS - "

[franswa3434]

Hey everyone,

Is it possible to create a button that will automatically print cell Range A336:K367, and for all worksheets who's sheet name begins with "QS - "?

For example, I have 4 sheets, QS - Warehouse, QS - HQ, Hardware, and QS - Store, and I need to print all the "QS - " sheets from A336:K367.

Also, the number of "QS - " sheets may very, and any text after "QS - " will vary as well.

I appreciate any and all help with this.

Thank you!

franswa3434

 

[AlphaFrog]

Try ActiveSheet inasted.

[COLOR=darkblue]Sub[/COLOR] Print_QS()
    
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Const[/COLOR] strFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "C:\Temp\Letter of Intent.pdf"
    [COLOR=darkblue]Dim[/COLOR] bReplace [COLOR=darkblue]As[/COLOR] Boolean: bReplace = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=green]'Select all QS sheets[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]If[/COLOR] ws.Name [COLOR=darkblue]Like[/COLOR] "QS - *" [COLOR=darkblue]Then[/COLOR]
            ws.PageSetup.PrintArea = "A336:K367"
            ws.Select Replace:=bReplace
            bReplace = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
    
    [COLOR=green]'Delete old file if exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(Dir(strFileName)) [COLOR=darkblue]Then[/COLOR] Kill strFileName
    
    [B]ActiveSheet[/B].ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        Ignoreprintareas:=False, _
        Openafterpublish:=[COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[franswa3434]

That worked perfectly, thank you!