Macro not working except in one tab, Must save and reopen to work in others.

I have a wierd issue. I have this macro for a workbook i use and if i run the macro in the first tab it runs and works fine. When i select the other tab and run it it gives me a debug error. If i close the worksheet save it and reopen it and run it in the other tab it runs fine.

So in otherwords i have to run the macro in the tab desired, save it , and always close and reopen the file to run it in the other tabs. I know the code is correct because it runs flawlessly without errors but i must be missing something.

If anyone can help that would really save me some time and efficiency.

Also i currently have the macro to run on the active tab/worksheet. Can i have it run on multiple tabs? i have 15 20 and 30 tabs in this example file but in my real file i have many more milage intervals ( some tabs i would not want to select ). It gets tedious having to select one tab at a time, run the macro, rinse and repeat.


For Example Run Macro First, in the first tab. It works perfectly. Go to the second tab and run the first macro. It gives a debug error. If you save the file and reopen it and go to teh second tab and run the macro again it works.


Thanks for anyone that helps, File included below.

To run it in multiple tabs, could go about that a couple ways.
If it's a changing worksheet with additions, I'd go with something like:

Dim wks as worksheet
for each wks in worksheets
    if wks.name = "Whatever" then
        code here
    endif
next wks

I replaced your FINDIT line with :

Dim x As Integer
  x = 1
  Do Until InStr(1, ActiveSheet.Cells(x, 1), "Car", vbTextCompare) > 0 Or _
    x > 10000
    x = x + 1
  Loop

And it works on all the worksheets without reopening.
I've never used FINDIT so I'm not sure how it determines which sheet it's running on, but that was your issue. FINDIT was not working after running once, and because it could not find "CAR", FINDIT.ROW was causing an error, since FINDIT was nothing.

Sorry, should have said add that in to replace the FINDIT line of code, then in the next couple lines there's a ...FINDIT.row - 1... which needs to be replaced with... x - 1

Can't seem to attach it, this is the complete code:

Sub First()
  Dim x As Integer
  x = 1
  Do Until InStr(1, ActiveSheet.Cells(x, 1), "Car", vbTextCompare) > 0 Or _
    x > 10000
    x = x + 1
  Loop
  Range("A2:BC" & x - 1).Sort key1:=Range("C2"), order1:=xlAscending, key2:=Range("D2"), order2:=xlAscending, key3:=Range("E2"), order3:=xlAscending, Header:=xlYes
  

  Dim C As Variant
  Dim Cell As Range
  Dim Data As Variant
  Dim DSO As Object
  Dim LastRow As Long
  Dim R As Long
  Dim StartRow As Long
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    StartRow = 3
    
   'Find the Sort column
    Set Cell = Wks.Rows(2).Find("sort", [A2], xlFormulas, xlWhole, xlByColumns, xlNext, False)
      If Not Cell Is Nothing Then
         C = Cell.Column
      Else
         MsgBox "Sort Column Not Found."
         Exit Sub
      End If
    
   'Find the last row marker. The number four.
    Set Cell = Wks.Columns(C).Find(4, Wks.Cells(3, C), xlFormulas, xlWhole, xlByRows, xlNext, False)
      If Not Cell Is Nothing Then
         LastRow = Cell.Row
      Else
         MsgBox "Last Row Marker Not Found."
         Exit Sub
      End If
    
      Set DSO = CreateObject("Scripting.Dictionary")
      DSO.CompareMode = vbTextCompare
    
         For R = StartRow To LastRow
          Data = Trim(Wks.Cells(R, "C") & Wks.Cells(R, "D") & Wks.Cells(R, "E"))
          If Data <> "" Then
            If Not DSO.Exists(Data) Then
               DSO.Add Data, R
               With Wks
                 .Range(.Cells(R, "B"), .Cells(R, "E")).Interior.ColorIndex = 6
               End With
            End If
          End If
        Next R
        
      Set DSO = Nothing
      

'
' CDEformat Macro
'

'
    Columns("E:E").Select
    Range("E2").Activate
    Selection.Copy
    Columns("K:K").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Dim CellColor As Integer, _
        R1 As Range, _
        R2 As Range, _
        CellAddress As String, _
        CellCount As Integer
    
    Set R1 = Range("K3:K1000")
    CellCount = 1
    For Each R2 In R1
        If R2.Offset(0, 1).Value = 4 Then Exit For Else
        If (R2.Interior.ColorIndex) = 6 Then
            CellAddress = Cells(1, CellCount).Address(False, False)
            CellAddress = Left(CellAddress, Len(CellAddress) - 1)
            R2.Value = CellAddress
            CellCount = CellCount + 1
        End If
    R2 = CellAddress
    Next R2

  Set car = Range("A:A").Find(what:="Car", LookIn:=xlValues)
  Range("A3:BC552").Sort key1:=Range("L3")
  

  holder = 1
  For Each ce In Range("K3:K" & Cells(Rows.Count, "K").End(xlUp).Row)
    If ce <> "" Then
      If Range(ce.Value & 1).Column > Cells(1, holder).Column Then holder = Range(ce.Value & 1).Column
    End If
  Next ce
  Range("M3").Resize(holder, 1).Formula = "=$H$1&$I$1 & SUBSTITUTE(ADDRESS(1, ROW() - 2, 4), ""1"", """")"

End Sub