Need help coding macro to format page size

[cobramonkey]

I'm a noob, in every way, and need some help!

I have a workbook that the different sheets each need to print in a different size format. One page (my certificate) needs to be printed in standard A4 format and saved as a PDF, but if I have printed another sheet (my valve tag) to our card printer, then try to save the certificate as a PDF, it saves it in the wrong size format. To manually fix this I must select the A4 printer in print screen, then save the PDF. So I'm attempting to cobble together a VBA script that will chose the correct printer, but not print, then prompt me which directory to save the PDF and then save it.

Sub PDFActiveSheet()

    Dim myprinter As String
    Dim printer_name As String
    printer_name = "HP LaserJet 400 M401 PCL 6"


    myprinter = Application.ActivePrinter
    Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
    Application.ActivePrinter = myprinter


   Dim ThisWorkbookName As String
   Dim ThisWorkbookPath As String
   Dim PDFFile As String
   
   On Error GoTo ErrHandler
   
    frmCertOptions.Show
    If frmCertOptions.mCancelled = True Then Exit Sub
    
    ThisWorkbookName = Replace(Excel.ActiveWorkbook.Name, ".xlsm", ".pdf")
    ThisWorkbookPath = Excel.ActiveWorkbook.Path
    PDFFile = ThisWorkbookPath & "\" & ThisWorkbookName
  
    Application.ScreenUpdating = False
    Rows("1:5").Select
    Selection.EntireRow.Hidden = True
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        PDFFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    
    ActiveWindow.SmallScroll Down:=-3
    Cells.Select
    Range("A6").Activate
    Selection.EntireRow.Hidden = False
    Range("B17:D17").Select
    
    Application.ScreenUpdating = True


Exit Sub


ErrHandler:
    Application.ScreenUpdating = True
    MsgBox "An error occured while attempting to create file: " & PDFFile & ". Make sure the file is not already open."


End Sub

1.) It autosaves to the same directory as the .xlsm file it came from, I want to be able to manually designate a different folder at the point of PDF creation.


2.) The printer setup. One sheet (certificate) is printed A4 size, and another sheet is printed to the Zebra card printer, which is in a different size format. So when I print to the card printer, and then go to the certificate sheet and create a PDF, it's reformatting the size and layout to the dimensions for the card printer. The way to correct the layout is to select in the print screen the default/A4 size printer, cancel print, then save as PDF. So I added this code I found online:


Dim myprinter As String
Dim printer_name As String
printer_name = "HP LaserJet 400 M401 PCL 6"


myprinter = Application.ActivePrinter
Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
Application.ActivePrinter = myprinter



And it errors out at the underlined line, saying "Run Time Error 424 Object Required"


What do I have wrong?


I also don't know if I combined those two codes correctly. I just pasted the above code before the PDF code.


So to recap, what I want the code to do is to format to the paper size of the HP LaserJet 400 M401 PCL 6 printer (format to that size but NOT PRINT), ask me where to save the PDF, generate the PDF and display it.



Thanks in advance for any help.