0

I have created the macro below to format a worksheet, add headers based on user input boxes then export the worksheet to a pdf. The macro works fine when I step through it, but when I run the full macro, it picks up the header values that were input during the previous run. Does anyone have any ideas as to why this might be?

Private Sub pFormatPrint()
    Dim Rep As String
    Dim Period As String
    Dim Fname As String
        
    Rep = InputBox("Please input REP", "REP")                                               'Header 1
    Period = InputBox("Please input Reporting Period", "Commission Reporting Period")       'Header 2
    Fname = InputBox("Please input last day of report period as YYYY.MM.DD", "File Name")   'to be used in pdf file name
    
    Sheets("Query").Select
    
    'set header based upon variables defined from user input box
    Application.PrintCommunication = False
            With ActiveSheet.PageSetup
                .CenterHeader = _
                "&""Georgia,Bold""OJM Group, LLC" & Chr(10) & Rep & "" & "Commissions" & Chr(10) & Period
                .FitToPagesWide = 1
                .FitToPagesTall = 10
            End With
        
    'format report
     ActiveSheet.ListObjects("pia_commissions").ListColumns(5).Range.Select
     Selection.ColumnWidth = 13
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = True
        End With
     ActiveSheet.ListObjects("pia_commissions").ListColumns(6).Range.Select
     Selection.ColumnWidth = 13
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = True
        End With
     ActiveSheet.ListObjects("pia_commissions").ListColumns(7).Range.Select
     Selection.ColumnWidth = 13
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = True
        End With
    
     'Export report to PDF
     ActiveSheet.ListObjects("pia_commissions").Range.Select
     Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Comm " & Rep & Fname
     
    Application.PrintCommunication = True

End Sub
Warcupine
  • 4,460
  • 3
  • 15
  • 24
AMS0111
  • 1
  • 1
  • 2
    I am assuming you are referring to the `.CenterHeader` ? Also a must read for you [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Oct 12 '20 at 14:51
  • Yes, I am referring to the .CenterHeader. I have to run the macro twice for it to pick up the new data from the Input Box, but if I step through the macro using F8 it picks it up the first time. Also, thank you for the link to learning how to avoid "Select" – AMS0111 Oct 26 '20 at 15:45

0 Answers0