Excel VBA with Advanced Filter Error messages

[snb]

Why don't you post a sample workbook ?

What is the value of variable Vregion ?

Why are columns B to D empty in Row 7 ?

In a criteria range the column'names' should match those of the to be filtered range.
The criteria range will be automatically given a name after mentioning it in the advancedfilter mathod.
No need to name it yourself.

Do not use spaces in a column'name'.

In principle this code suffices:

Sub M_snb()
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .SetText Join(Array(Join(Array("MS", "SOH", "Order", "RP", "Format", "Region"), vbTab), Join(Array("4", 0, 0, "Roster", "Corporate", VRegion), vbTab), Join(Array("", "", "", "", "Hyper", ""), vbTab)), vbCr)
    .PutInClipboard
  End With

  with Sheet1
    .Paste .Cells(100, 1)
    .ListObjects(1).Range.AdvancedFilter 2, .Cells(100, 1).CurrentRegion, sheet2.Cells(1)
    .Cells(100, 1).CurrentRegion.ClearContents
  end with
End Sub