Minor Bug in Existing Code

[z3115]

EDIT: Solved


Hey all,

This is probably something obvious I'm missing, but I've been staring at it all morning and can't figure it out.

The code below pulls a bunch of rows from multiple sheets and puts them all together in the "To Do - Ind." sheet. If that sheet doesn't exist, it creates it.

The macro works fine if the "To Do - Ind" sheet already exists. The problem is if the sheet does not already exist, it creates it and then skips the part where it pulls in info from other sheets into the To Do sheet.

Do I have an end if in the wrong spot or something?

Thanks for the help!

    Dim UsdRws As Long
    Dim OSht As Worksheet
    Dim Ws As Worksheet
    Dim Ans As String
    Dim Ary As Variant
    Dim Ans2 As String
    
    Ans2 = "Yes" 'Ans2 = the criteria you want to to filter on, in this case key date = yes
    
    Application.ScreenUpdating = False


    'sheets to igonre:
    Ary = Array("To Do - Ind.", "Summary", "Named Ranges", "Custom Reports", "To Do - Team")
    On Error Resume Next
    Set OSht = Sheets("To Do - Ind.")
    On Error GoTo 0
    
    If OSht Is Nothing Then
        Sheets.Add(Before:=Sheets(1)).Name = "To Do - Ind."
    Else: OSht.Cells.ClearContents
    End If
    
    Ans = Range("cr_name").Value 'InputBox("Please enter a name")
    If Len(Ans) = 0 Then
        MsgBox "Nothing Selected"
        Exit Sub
    End If
     
    'OSht.Rows(1).Value = Sheets("BOD").Rows(1).Value
    Sheets("To Do - Ind.").Rows(1).Value = Sheets("BOD").Rows(1).Value
    
    For Each Ws In Worksheets
        If Not UBound(Filter(Ary, Ws.Name, True, vbTextCompare)) >= 0 Then
            If Ws.Visible = xlSheetVisible Then
            UsdRws = Ws.Range("J" & Rows.Count).End(xlUp).Row
            If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
            Ws.Range("A1").AutoFilter field:=10, Criteria1:=Ans
            If Range("fc_1").Value = 2 Then
            Ws.Range("A1").AutoFilter field:=1, Criteria1:=Ans2 'Filter on key dates if only pulling key dates
            End If
            On Error Resume Next
            Ws.Range("A2:A" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
                OSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
            On Error GoTo 0
            Ws.Range("A1").AutoFilter
        End If
        End If
    Next Ws

 

[z3115]

NVM, fixed it, needed to re-set the variable OSht within the if statement that created the new To Do sheet if it did not already exist.