How to change this printing macro

terrymac · #1 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.

Graham Griggs · #2 12-01-2015, 06:47 PM

Quick and dirty is thus:

Sub select_tabs_to_print()
Count = ActiveWorkbook.Sheets.Count
For nowsheet = 1 To Count
If Sheets(nowsheet).Cells(1, 1) = "PRINT" Then
'print out the worksheet
Else
'dont print out the worksheet
End If
Next nowsheet

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Possible Macro to read and change name whilst printing certificate	DaZ	Excel Programming	5	09-06-2014 10:50 AM
Possible Macro to read and change name whilst printing certificate	DaZ	Word VBA	3	09-05-2014 05:31 PM
Section break makes style change when printing or saving as pdf	bohk	Word	4	06-07-2013 02:34 PM
Change sidefoot when printing?	KenZu	Word	0	06-21-2011 05:16 AM
Change Display Name when printing	Lucky7MQ	Outlook	0	12-10-2005 10:41 PM