0

I have an excel with multiple worksheets. Each worksheet contains either a graph or a table. I wrote a code that loops through the worksheets, detects whether it is a table or not (by checking cells A8 and A9 on the worksheet) and if so, apply an autofilter on that table. However, if I execute this code, the code works fine for the first worksheet, but returns an error message on the next run through the loop. I'm missing something, but cannot figure out what. Any help is appreciated.


Dim sheet As Worksheet

For Each sheet In ActiveWorkbook.Worksheets

With sheet

    If Range("A8") = "Flag" And IsEmpty(Range("A9").Value) = False Then

        Dim LastCol As Integer

        LastColumn = Cells(8, Columns.Count).End(xlToLeft).Column

        .Range("A8", Cells(8, LastColumn)).AutoFilter

    End If

End With

Next sheet

End Sub
JvdV
  • 70,606
  • 8
  • 39
  • 70
Weathus
  • 43
  • 1
  • 2
  • 5
  • 2
    You need a period in front of every `Range` and `Cells` and `Columns` call within the `With`. – BigBen Jan 15 '20 at 13:03
  • Thanks, I read your reference, removed my with statements and added "sheet." before each range and cells statement. That fixed the issue. – Weathus Jan 15 '20 at 13:18
  • I would have kept the `With` if I were you... makes for a lot less typing :) – BigBen Jan 15 '20 at 13:18
  • 1
    seems like I could've also kept the with statement. I only saw your second comment now. I'll try that too. Anyway, thanks a lot! – Weathus Jan 15 '20 at 13:19

0 Answers0