OzGrid

How to use VBA code to print number of copies based on cell value

Requirement:

The user has found a code on the forum to print copies based on cell value, but the user needs to be able to specify what sheet to print.

So the user inputs a number in cell on sheet 1, presses the button with assigned macro that would print out that number of copies of Sheet 2.

Sub Macro3()

    For Each Worksheet In ActiveWorkbook.Worksheets

        With Sheets(Worksheet.Name)

            If .Visible = xlSheetVisible And _
                IsNumeric(.Range("A1").Value) = True And _
               .Range("A1").Value > 0 Then
                    .PrintOut Copies:=.Range("A1").Value, _
                     IgnorePrintAreas:=False
            End If

        End With

    Next Worksheet

End Sub

Solution:

Option Explicit

Sub PrintOut_Method_All_Parameters_Listed()
'List of all of the optional parameters for printing an Excel spreadsheet using the PrintOut method

Worksheets.PrintOut _
From:=Range("F8"), _
To:=Range("F9"), _
Copies:=Range("F10"), _
Preview:=False, _
ActivePrinter:="", _
PrintToFile:=False, _
Collate:=True, _
{PrToFileName:="", _
IgnorePrintAreas:=False

End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Logit.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery