arraysexcelvbaprintingvba7

How to set the print area of an array of worksheets?


Good day. I was creating a macro where i set an array of worksheets to have a certain print area. Unfortunately the error "Runtime Error '438' Objcects dosent support this property or method" appears.

Here is my code.

Option Explicit
Dim NumberRow As String
  
Sub PrintFront()
'
' PrintFront Macro
'
' Keyboard Shortcut: Ctrl+Shift+L

NumberRow = ThisWorkbook.Sheets("Admin Panel").Range("N21").End(xlDown).Row


Dim wb As Workbook: Set wb = ThisWorkbook
Dim MyArray As Variant: MyArray = Application _
.Transpose(wb.Sheets("Admin Panel").Range("N21:N" & NumberRow).Value)


ThisWorkbook.Sheets(Array(MyArray)).PageSetup.PrintArea = "C4:AF45"
wb.Sheets(MyArray).PrintPreview

Here is the line that caused the error:

ThisWorkbook.Sheets(Array(MyArray)).PageSetup.PrintArea = "C4:AF45"

I tried using this but did not work.

wb.Sheets(MyArray).PageSetup.PrintArea = "C4:AF45"

Solution

  • Dim wb As Workbook: Set wb = ThisWorkbook
    Dim NumberRow As Long: NumberRow = wb.Sheets("Admin Panel").Range("N21").End(xlDown).Row
    Dim MyArray As Variant
    If NumberRow = Rows.Count Then
        MyArray = Array(wb.Sheets("Admin Panel").Range("N21").Value)
    Else
        MyArray = Application _
        .Transpose(wb.Sheets("Admin Panel").Range("N21:N" & NumberRow).Value)
    End If
    Dim vSht As Variant
    For Each vSht In MyArray
        wb.Sheets(vSht).PageSetup.PrintArea = "C4:AF45"
    Next
    wb.Sheets(MyArray).PrintPreview