VBA for print if cell contains, then choose printer. Have code just need advice.

[lukestkd]

Hello all,
I have the following macro on a button which was bringing up the choose printer dialog then printing when printer chosen, however now it is printing on the last printer used and then bringing up the dialog. How can I correct this?

I want to press the button, choose the printer, then print. It only prints sheets that have "YES" in cell E1.

Sub PrintSheets()Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Range("E1")) = "YES" Then ws.PrintOut
    Next ws
   Application.Dialogs(xlDialogPrinterSetup).Show


End Sub

I also want to add this formula, which puts the value in A2 in the header. How can I build this into the code above?

Sub UpdateHeader()ActiveSheet.PageSetup.CenterHeader = Range("A2").Value
End Sub

What I ideally want is it to put the value from Sheet'Menu' Range A2 into the header of all sheets. I've also noticed that if something in A2 changes, the macro doesn't replace a previous entry, it keeps it and writes over it. Is there a way to get it to clear the header before running this bit of code - so it has a blank canvas.

Any advice appreciated!

Cheers

Luke

[JBeaucaire]

Untested, but maybe:

Option Explicit

Sub PrintSheets()
Dim ws As Worksheet

    Application.Dialogs(xlDialogPrinterSetup).Show
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Range("E1")) = "YES" Then
            ws.PageSetup.CenterHeader = ws.Range("A2").Value
            ws.PrintOut
        End If

    Next ws

End Sub

[vfxd]

Try this:

Sub PrintSheets()
    
    Dim i               As Long
    Dim sSheetNames()   As String
    Dim ws              As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Range("E1")) = "YES" Then
            i = i + 1
            ReDim Preserve sSheetNames(1 To i)
            sSheetNames(i) = ws.Name
            ws.PageSetup.CenterHeader = ""
            ws.PageSetup.CenterHeader = ws.Range("A2").Value
        End If
    Next ws
    Sheets(sSheetNames).Select
    Application.Dialogs(xlDialogPrinterSetup).Show
    Application.Dialogs(xlDialogPrint).Show
End Sub

[lukestkd]

This works perfectly for print!

Is there a way to make it print out straight after Application.Dialogs(xlDialogPrinterSetup).Show?

[lukestkd]

Also, is there a way to change the formatting in the header so it is font TwCen, bold, Size 14?

[JBeaucaire]

My version pops the dialog box first, then does the printouts automatically.

[lukestkd]

My version pops the dialog box first, then does the printouts automatically.

Thank you JB. Is there a way for your code to combine the printed sheets into one print job?

[JBeaucaire]

I fear you are not reading these macros carefully and absorbing what they are doing and when/how. The .Show is a single line of code, certainly you can see in my macro where that occurs, and then where my macro prints the jobs one sheet at a time.

Certainly you can see in the other macro where those codes are and move them appropriately? This is hard to read at first, but you can do it. Post up your working successful macro, or post what you've tried if it fails.

[lukestkd]

I fear you are not reading these macros carefully and absorbing what they are doing and when/how.

Fair comment!

I tried:

Sub PrintSheetsJbeau()
    
    Dim i               As Long
    Dim sSheetNames()   As String
    Dim ws              As Worksheet

    Application.Dialogs(xlDialogPrinterSetup).Show
  For Each ws In ActiveWorkbook.Worksheets
           If UCase(ws.Range("E1")) = "YES" Then
            ws.PageSetup.CenterHeader = ws.Range("A2").Value
                Sheets(sSheetNames).Select

            ws.PrintOut
        End If

    Next ws

End Sub

I am still very new to VB (one week!), I'm stuggling to identify what line of code in vfxd's groups it into one print job?

[JBeaucaire]

You're trying to use My macro to do what his does. Mine doesn't do that. Mine is a good example of doing the dialog show first, which you seem to have gotten.

Now, I would say you use the rest of his macro, not mine. Plus, I see there is a new line of code needed, his code "selects" all the sheets, the added line now prints them all at once with the printer you've already selected:

Option Explicit

Sub PrintSheets()
Dim i               As Long
Dim sSheetNames()   As String
Dim ws              As Worksheet

    Application.Dialogs(xlDialogPrinterSetup).Show
    
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Range("E1")) = "YES" Then
            i = i + 1
            ReDim Preserve sSheetNames(1 To i)
            sSheetNames(i) = ws.Name
            ws.PageSetup.CenterHeader = ""
            ws.PageSetup.CenterHeader = ws.Range("A2").Value
        End If
    Next ws
    Sheets(sSheetNames).Select
    ActiveWindow.SelectedSheets.PrintOut

End Sub