Print Button In Excel 2016 Using Macros?

Question

I'm currently trying to create a Print Button on one of my worksheets. I need it to print that worksheet as well as another one. Both of the names are "Budget Sheet" and "Listed Commitments Sheet" without the quotation marks.

I created the button without hassle, but I know very little about Macros so I still need the code. I've tried multiple solutions, but nothing seems to work. I've recently tried to use this Code, but it hasn't worked. What am I doing wrong? What code could I possibly use instead?

Private Sub CommandButton1_Click()

Function PrintMultipleSheets()
    Sheets(Array("Budget Sheet", "Listed Commitments Sheet")).PrintOut
End Function

End Sub

Instead of an image, can you paste the code here (and format with the code tags, `{}`)? Also, what isn't working, is there an error? Is it just not printing, etc.? — BruceWayne, Jul 07 '17 at 15:50
It comes up with an error that says "Compile Error: Expected End Sub". — Ash, Jul 07 '17 at 16:01
You put a function inside a sub. I don't think you can do that. — BruceWayne, Jul 07 '17 at 16:04
Get ride of the line `Function PrintMultipleSheets()` and get rid of `End Function`. It should work I think. — David Zemens, Jul 07 '17 at 16:04
Cheers @Ash do consider marking my answer (below) as "Accepted". — David Zemens, Jul 07 '17 at 16:15

score 0 · Answer 1 · answered Jul 07 '17 at 16:02

0

Just a simple loop with a print call:

Sub forEachWs()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Call printSheet(ws)
    Next
End Sub

Function pasteContents(ws as Worksheet)
     ActiveSheet.PrintOut
End Function

answered Jul 07 '17 at 16:02

ERT

719
5
16

score 0 · Accepted Answer · answered Jul 07 '17 at 16:07

It comes up with an error that says "Compile Error: Expected End Sub".

The code doesn't compile, because you can't have a Function inside a Sub.

Get ride of the line Function PrintMultipleSheets() and get rid of End Function. It should work I think. You'll end up with:

Private Sub CommandButton1_Click()

Sheets(Array("Budget Sheet", "Listed Commitments Sheet")).PrintOut

End Sub

Print Button In Excel 2016 Using Macros?

2 Answers2