Microsoft Office Forums - View Single Post - [Solved] How to change this printing macro

terrymac · 11-13-2015, 03:16 AM

I have very limited macro knowledge but I've given my best shot at running a command.

For some background on the requirements; I have created a spreadsheet for our shipping department where based on a drop down box contents, once a button is pressed it will select certain sheets and save as a PDF, then print out # of copies based on a number in each open sheet.

However the list is getting pretty long and so far I am telling the system to run a different macro for each print requirement. What I would like is to change one section of the code below so it prints any sheets that are not hidden, where cell A1 has "PRINT" as the trigger.

The reason for this is because I have a few sheets in the workbook where data is entered but do not need printing.

This is my (probably very messy) command

Code:

Sub Macro1()

'This section enters 1 in a cell which triggers a signature to appear on the PDF about to print     
    Sheets("PRINT").Select
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "1"
     
     
    
    ThisFile = Range("FIELDS!B22").Value
    Sheets(Array("SHIPPING", "INVOICE01", "PACKING LIST")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile _
        , Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
        
            Sheets("PRINT").Select
    ActiveWindow.SmallScroll Down:=-51
    Range("B1").Select
    
        ThisFile = Range("FIELDS!B23").Value
    Sheets(Array("INVOICE01", "PACKING LIST", "AUSLETTER")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile _
        , Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
        
            Sheets("PRINT").Select
    ActiveWindow.SmallScroll Down:=-51
    Range("B1").Select



'This section enters 2 in a cell which hides the signature as we are now printing onto paper for an original signature      
      Sheets("PRINT").Select
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "2"
    
    
    
        For Each Worksheet In ActiveWorkbook.Worksheets
         
        With Sheets(Worksheet.Name)
    Range("PRINT!B15").Select
    ActiveCell.FormulaR1C1 = "2"
            If .Visible = xlSheetVisible And _
            IsNumeric(.Range("A1").Value) = True And _
            .Range("A1").Value > 0 Then
                .PrintOut Copies:=.Range("A1").Value
                 
            End If
             
        End With
         
    Next Worksheet
End Sub

Sorry about the length of this thread, and apologies if it isnt very clear.