I would like to hide some columns on the external workbook. Unfortunately, with the following code, I have an error: Error: 438 - Object doesn't support this property or method

 Option Explicit

 Sub NewWorksheet()

 Dim sFound As String, fPath As String
 Dim WB1 As Workbook

 fPath = ThisWorkbook.Path
 sFound = Dir(fPath & "\Advanced Risk Management Report*.xlsx")
 If sFound <> "" Then
    Set WB1 = Workbooks.Open(fPath & "\" & Found)


   With WB1
     .Columns("E:M").Hidden = True
     .Selection.EntireColumn.Hidden = True
     .Columns("O:W").Select
     .Selection.EntireColumn.Hidden = True
     .Columns("Y:AQ").Select
     .Selection.EntireColumn.Hidden = True
     'Dim exrng As Range
    'Dim exLastRow As Long
    'exLastRow = Range("C13").End(xlDown).Row
    'Set exrng = Range("C13:C" & exLastRow)
    'exrng.Sort key1:=Range("B13:X" & exLastRow), _
    'order1:=xlAscending, Header:=xlYes
   End With
  End If


 End Sub

Is there any way for hiding the columns at an external workbook?

I would like to hide some columns on the external workbook. Unfortunately, with the following code, I have an error: Error: 438 - Object doesn't support this property or method

 Option Explicit

 Sub NewWorksheet()

 Dim sFound As String, fPath As String
 Dim WB1 As Workbook

 fPath = ThisWorkbook.Path
 sFound = Dir(fPath & "\Advanced Risk Management Report*.xlsx")
 If sFound <> "" Then
    Set WB1 = Workbooks.Open(fPath & "\" & Found)


   With WB1
     .Columns("E:M").Hidden = True
     .Selection.EntireColumn.Hidden = True
     .Columns("O:W").Select
     .Selection.EntireColumn.Hidden = True
     .Columns("Y:AQ").Select
     .Selection.EntireColumn.Hidden = True
     'Dim exrng As Range
    'Dim exLastRow As Long
    'exLastRow = Range("C13").End(xlDown).Row
    'Set exrng = Range("C13:C" & exLastRow)
    'exrng.Sort key1:=Range("B13:X" & exLastRow), _
    'order1:=xlAscending, Header:=xlYes
   End With
  End If


 End Sub

Is there any way for hiding the columns at an external workbook?

• excel
• vba

• 1 You need to specify the sheet to work on. Something like With WB1.Worksheets("Sheet1") – cybernetic.nomad Commented Jan 20 at 17:34

1 Answer 1

WB1 is a workbook (an Excel file). A workbook has no columns - worksheets have columns. You need to specify the sheet where you want to hide columns

Set wb1 = Workbooks.Open(fPath & "\" & Found)
With wb1.Worksheets(1)
    .Columns("E:M").Hidden = True

or use a worksheet variable:

Set wb1 = Workbooks.Open(fPath & "\" & Found)
Dim ws1 As Worksheet
Set ws1 = wb1.Worksheets(1)   ' Change if you want to work on a different sheet
With ws1
    .Columns("E:M").Hidden = True