Advanced Filter for Unique Value Count

[Toddzilla24]

Hey guys,

I created a macro to pull a count of unique values from a list. I've been toying with it a bit, but can't seem to get it to function reliably. I just recently came back to working on it and continuously get this error: "The extract range has a missing or invalid field name." Basically I have a master sheet that contains all of the values, as well as a hidden sheet that pulls those values from the master and combines the values from separate cells into a single value in a different cell. None if this part is done in VBA. The VBA uses the advanced filtering function to spit out only the unique values, then another column uses a non-VBA countIF formula to get the quantities of these unique values. Then VBA again, pastes the count and unique values into the master sheet and re-hides the originally hidden sheet etc.

I've attached my code below for this. The red is where I'm getting the error currently. The only common solution I can find is that the headings are not set up properly. I know that this should be the case for me, since I have been successful with this, the way the headings are currently set up (the extract and output columns have the exact same header).

Sub AHUValveCount()    Application.ScreenUpdating = False
    
'
' AHUValveCount Macro
'


'
    Dim VCWS As Worksheet
    Dim VOWS As Worksheet
    Dim rownumber As Integer
    Dim copyrng As Range
    
   Set VOWS = Worksheets("AHU Valves - Master")
   Set VCWS = Worksheets("AHU Valve Counter")
   
  With VOWS
            .Unprotect Password:="###"
    End With
   
   With VCWS
            .Visible = True
            .Unprotect Password:="###"
            .Range("I5:I50").ClearContents
[COLOR=#ff0000]            .Range("G4:G50").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _[/COLOR]
[COLOR=#ff0000]        "I3:I50"), Unique:=True[/COLOR]
    End With
    
    rownumber = VCWS.Application.WorksheetFunction.CountIf(Range("H5:H55"), "<>0") - 1 + 4
    Set copyrng = VCWS.Range("H5:I" & rownumber)
    copyrng.Copy
    
    With VOWS
            .Range("C62:D111").ClearContents
            .Range("C62").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            .Protect Password:="###"
    End With
    Application.CutCopyMode = False
    With VCWS
            .Unprotect Password:="###"
            .Visible = False
    End With
        
    Application.ScreenUpdating = True
End Sub

I'm also wondering if maybe I'm doing this in a way that's more complicated than it needs to be. Any advice would be appreciated. Thanks in advance.

 

[Toddzilla24]

bump....