Really stuck ! Print to PDF won't format properly

[Iceshade]

Hi Everyone,

Firstly, very new to VBA so bare with me. I am trying to print 2 worksheets to the 1 PDF, formatted to all columns fit into 1 page but I just can't get it to work.

Here is my code, first it generates the sheets I need then does some formatting, it then calls next module to print:

Sub CopySheets()
'Declare
Dim sh As Worksheet, nsh1 As Worksheet, nsh2 As Worksheet

'Set and Create worksheets
Set sh = ActiveSheet
Set nsh1 = Sheets.Add(After:=Sheets("Instructions"))
Set nsh2 = Sheets.Add(After:=nsh1)
nsh1.Name = "Print Banker"
nsh2.Name = "Print IQ Checklist"

'Copy from Banker Checklist and paste to nsh1(Print Banker) + format for asthetics
sh.Activate
sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy
nsh1.Range("A1").PasteSpecial xlPasteColumnWidths
nsh1.Range("A1").PasteSpecial xlPasteValues
nsh1.Range("B:C").EntireColumn.Font.Bold = True
nsh1.Cells.Font.Size = 8
nsh1.Rows("1:3").Delete
nsh1.Rows("6").Delete
nsh1.Range("B1:D1").Font.Size = 12
nsh1.Range("B1:D1").Font.Underline = xlUnderlineStyleSingle
nsh1.Range("D1").Font.Bold = True
nsh1.Range("C:C").EntireColumn.WrapText = True
nsh1.Range("E:E").HorizontalAlignment = xlCenter
nsh1.PageSetup.Orientation = xlLandscape


'Copy from IQ Checklist and paste to nsh2(Print IQ Checklist) + format for aesthetics
Sheets("(4) IQ Checklist").Activate
Sheets("(4) IQ Checklist").UsedRange.SpecialCells(xlCellTypeVisible).Copy
nsh2.Range("A1").PasteSpecial xlPasteValues
nsh2.Range("A1").PasteSpecial xlPasteColumnWidths
nsh2.Range("B:B").EntireColumn.Font.Bold = True
nsh2.Cells.Font.Size = 8
nsh2.Range("B:B").EntireColumn.WrapText = True
nsh2.Range("B4").Font.Size = 12
nsh2.Range("B4").Font.Underline = xlUnderlineStyleSingle
nsh2.Rows("63:68").Delete
nsh2.Rows("1:3").Delete
nsh2.Rows("2:3").Delete
nsh2.PageSetup.Orientation = xlLandscape

Application.CutCopyMode = False

Call CompileReport
End Sub

This is where it generates the PDF and saves;

Sub CompileReport()
    Dim mySheets As Variant, sh
    Dim filename As Variant
    
        
    mySheets = Array("Print 1", "Print 2")
    For Each sh In mySheets
        Sheets(sh).PageSetup.Orientation = xlLandscape
        Sheets(sh).PageSetup.FitToPagesWide = 1
        
                
    Next

    Sheets(mySheets).Select
    
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:="H:\Desktop\Test" & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

End Sub

Could really use the help please, I have been stuck on this for over a week, I just can't see why it isn't working.

Thank you !

 

[ChrisFoster]

When you say it doesn't work, what actually happens?