Auto filter a column when file is opened

[russelldt]

I have a file that has a set value on Sheet 1 Cell A1.

Sheet 2 contains a table of data that is updated from a separate file, so each time the first file is opened, the data is updated.
Row 3 of sheet 2 contains a list of table column headings. One column (3) needs to be filtered to only show the value on Sheet1, Cell A1. It's a simple Filter operation (clear filter and filter again) to update Sheet 2's values.

I want to find out if this filter operation can be performed automatically each time i open the file - either with a Macro or VB script.

Thanks

 

[Peter_SSs]

Try this in the ThisWorkbook module

Private Sub Workbook_Open()
  With Sheets("Sheet2")
    .AutoFilterMode = False
    .Range("C3", .Range("C" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("A1").Value
  End With
End Sub

 

[russelldt]

Try this in the ThisWorkbook module

View attachment 70195

Private Sub Workbook_Open()
  With Sheets("Sheet2")
    .AutoFilterMode = False
    .Range("C3", .Range("C" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("A1").Value
  End With
End Sub

Thanks Peter. I get a run time error on the second line. I have tried "SheetInvoice log" as well (without the space) and i get the same error message.


Private Sub Workbook_Open()
With Sheets("Sheet Invoice log")
.AutoFilterMode = False
.Range("C3", .Range("C" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=Sheets("Sheet Cashflow input").Range("E3").Value
End With
End Sub

 

[russelldt]

Thanks Peter. I get a run time error on the second line. I have tried "SheetInvoice log" as well (without the space) and i get the same error message.


Private Sub Workbook_Open()
With Sheets("Sheet Invoice log")
.AutoFilterMode = False
.Range("C3", .Range("C" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=Sheets("Sheet Cashflow input").Range("E3").Value
End With
End Sub

My error, I forgot to remove the "Sheet" from the script.

Works like a charm.

Thanks

 

[Peter_SSs]

You're welcome. Glad you got it sorted.