Fixing Type Mismatch Error in argument passing

**josel2820** · 04-22-2013, 02:54 PM

I'm passing a ListObject to a macro but I bump into a probably obvious error, I clearly missed something.

I run a vba macro in Excel 2007

The exact error I get is "Compilation Error: Type Mismatch" This happens when I am passing a ListObject as argumento to the macro
CopyFilteredTable.

Below you find the macro

Thanks in advance:

Public Sub FilterTable()

Dim FilteredTable As ListObject

Worksheets("Sheet1").Select

Set FilteredTable = ActiveSheet.ListObjects("table1")
FilteredTable.Range.AutoFilter Field:=4, Criteria1:=""
CopyFilteredTable (FilteredTable)


 End Sub

Sub CopyFilteredTable(table As ListObject)
  
End Sub

**Sbarro79** · 04-22-2013, 03:55 PM

From MS Excel VBA Help:

If you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist.

**josel2820** · 04-22-2013, 04:40 PM

Thanks Sbarro79.

**xladept** · 04-22-2013, 04:50 PM

I noticed that there is a space between the call and the argument - that should not be - I've found that you don't need the "Call" keyword when there is only one argument - so, I think that if you omit the space, then the code will work????

**Sbarro79** · 04-22-2013, 05:13 PM

Hi xladept

I tried your suggestion and removed that space, but Excel put it back again, so it did not work.
Apparently it must be like help says:
you use Call keyword - procedure name, no space, argument(s) in parentheses
you don't use Call keyword - procedure name, space, argument(s) with no parentheses

**xladept** · 04-22-2013, 05:23 PM

@Sbarro

Thanks for trying it out. I've still got the NB mindset

**snb** · 04-22-2013, 05:38 PM

Why not simply:

Sub M_snb()
    With Sheets("Sheet1").ListObjects("table1").Range
        .AutoFilter 4, ">0"
        .Copy Sheets("Sheet2").Cells(1)
        .AutoFilter
    End With
End Sub