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]

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

[color=darkblue]Sub[/color] Print_QS()
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [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.PrintArea = "A336:K367"
            ws.PrintOut Copies:=1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] ws
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[franswa3434]

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

When I try this, i get the following error:

Compile error:

Method or data members not found


It then highlights (in yellow) my sub line, and then in blue, highlights ".PrintArea = "

Private Sub CommandButton4_Click()


'Sheet9.Visible = True
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "QS - *" Then
ws.PrintArea = "A336:K387"
ws.PrintOut Copies:=1
End If
Next ws
'Sheet9.Visible = False

End Sub

 

[AlphaFrog]

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

Sorry. It should be this...

[COLOR=darkblue]Sub[/COLOR] Print_QS()
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [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[B].PageSetup[/B].PrintArea = "A336:K367"
            ws.PrintOut Copies:=1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[franswa3434]

That worked perfectly, thank you so much!

 

[franswa3434]

One last (hopefully easy) question.

Instead of printing it, i would ideally like to either export it as a PDF, or print as a PDF.

I tried to do the following below, but it is not working. Thoughts on how to do this?

Sub Print_QS()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "QS - *" Then
ws.PageSetup.PrintArea = "A336:K367"
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Temp\Letter of Intent.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, ignoreprintareas:=False, _
openafterpublish:=True

End If
Next ws
End Sub

 

[AlphaFrog]

Do you want one PDF file with a page for each QS sheet or multiple files of one page each?

 

[franswa3434]

I would like all of the QS sheets on a single PDF file, please

 

[AlphaFrog]

Try this...

[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
    
    Selection.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]

Thank you.

It will give me 3 pages in a PDF, but the pages are blank?