VBA Code Not Working Consistently - Run Time Error 91

[shellp]

The following code is to take data from the "raw" worksheet and copy to separate workbooks based on the initials of each coder. When the Excel document is open and the macro selected, I get a run-time error 91 "Object variable or With block variable not set" but when I run it a second time it works exactly as planned (so it runs correctly every other time it is selected). The error occurs on line 18: "set Rng = sht.range(sht.autofilter.range(columns(4).address)". Any assistance greatly appreciated.

Sub Coder_Own_Sheet()
    Dim Sht As Worksheet
    Dim Rng As Range
    Dim List As Collection
    Dim varValue As Variant
    Dim i As Long
    Dim filename As String
    Dim pathname
      
   Set Sht = ActiveWorkbook.Sheets("Raw")
   filename = "Cell Saver_" & Format(Sheets("General").Range("A1").Value, "MMM-yyyy") & "_" & varValue
   pathname = Sheets("General").Range("A2")
  
   With Sht.Range("A3")
   .AutoFilter
  End With
    
Set Rng = Sht.Range(Sht.AutoFilter.Range.Columns(4).Address)
Set List = New Collection

   On Error Resume Next
   For i = 2 To Rng.Rows.Count
    List.Add Rng.Cells(i, 1), CStr(Rng.Cells(i, 1))
   Next i
  For Each varValue In List
 Rng.AutoFilter Field:=4, Criteria1:=varValue

'       // Copy the AutoFiltered Range to new Workbook
    Sht.AutoFilter.Range.Copy
    Workbooks.Add
    Range("A1") = "Inpatient Case Review for Flagged Interventions - Cell Saver"
    Range("A2") = "Fix the error please"
    Range("A4").PasteSpecial xlPasteAll
    Range("A1").Font.Size = 16
    Range("A1").Font.Bold = True
    Columns("A").ColumnWidth = 14
    Columns("B:H").EntireColumn.AutoFit
    ActiveWorkbook.SaveAs filename:=pathname & filename & varValue & ".xlsx"
    ActiveWorkbook.Close savechanges:=True

'   // Loop back to get the next collection Value
   Next varValue

'   // Go back to main Sheet and removed filters
    Sht.AutoFilter.ShowAllData
    Sht.Activate
End Sub

 

[rollis13]

The only way to replicate your issue is to NOT declare the variable: Rng. Are you using Option Explicit ?

 

[shellp]

Thanks for your response. No I wasn't using Option Explicit but adding it results the same...first attempt fails and second attempt works. Is there any other way to write this so it works on the first try?

 

[rollis13]

Change this part of your macro to this:

With Sht
    .AutoFilterMode = False
    .Range("A3").AutoFilter
End With

 

[shellp]

You are amazing, thank you so much, that totally fixed it!!

 

[rollis13]

Glad having been of some help.