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"
PageSetup.PrintArea
setting on Sheets
object/collection (multiple sheets).For
loop to apply PageSetup.PrintArea
setting for each sheetNumberRow
before using it. It will be the max row number on sheet if there isn't any data under cell N21.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