CODEWITHSUNDEEP
vbaexcel
doriansm
doriansm

Reputation: 245

Issue re-applying a filter on workbook close

I have the following code in the ThisWorkbook section:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet("Sheet2").AutoFilter.ApplyFilter
    ActiveWorkbook.Save
    ThisWorkbook.Saved = True
End Sub

I assumed the second line would re-apply the filter I have set in the AutoFilter on Sheet 2.

I have data feeding in to Sheet 1, and Sheet 2 reads that data and filters it. The document is then working with AutoHotkey to: open, take more data, save and close on a timer. I just need it so that when the document closes it looks at that filter on Sheet2 and re-applies it.

I have tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet2.AutoFilter.ApplyFilter
    ActiveWorkbook.Save
    ThisWorkbook.Saved = True
End Sub

But that did not work. After looking through some VBA stuff, it seemed changing Sheet2.AutoFilter.ApplyFilter to Sheet("Sheet2").AutoFilter.Applyfilter would do the trick. But now I just get Compile Error: sub or function not defined.

Upvotes: 1

Views: 269

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Sheet("Sheet2").AutoFilter.Applyfilter should be Sheets("Sheet2").AutoFilter.Applyfilter (missing the "s"). Sheet isn't something recognized by VBA, so it thinks that's a subroutine or function you defined somewhere else. Sheets() however, is what you're looking for.

Sheet2.[whatever] does have applications in VBA, however it's NOT necessarily going to refer to the sheet named "Sheet2". Sheet2 refers to the second worksheet created in the workbook. If you create three worksheets, and rename them to like "Fun Page 1", "Fun Page 4", "Fun Page 8", Sheet2.AutoFilter... will run on whatever sheet you created second (which could be the one named "Fun Page 4".)

By using Sheets("Fun Page 4").AutoFilter..., the code is telling VB to look for the sheet named "Fun Page 4", not the one that was created 1st, 2nd, etc...

Does that make sense?

Upvotes: 2

Related Questions