Macro to filter a query based on user input

[suttorpa]

Hello,

I never excel scripted before.. so I am looking for some help with a macro, I created a workbook with a company inventory of all IT items.. each sheet has a listing of a certain type of device (workstations/switchs/keyboards/monitors/printers ect). I have a query that is activated by a macro that pulls all devices into one sheet with just a few related columns such as "location, serial number, model number, ect". The query works, but I am now trying to create a macro that accepts a users input of location and filters the query based on that so I can easily see what devices belong in which location. Here is the macro VBA as it is now (everything in black works, color Items I have added, the orange script works to query input, but the red script is creating an error.. I think it may be incorrect sequence, can anyone help?:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Dim myValue As Variant
myValue = InputBox("Give me some input")
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Query Entire Inventory""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Query Entire Inventory]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Query_Entire_Inventory"
.Refresh BackgroundQuery:=False
.ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=Location.Column, Criteria1:="myValue"
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[mumps]

Try using "myvalue" without the quotation marks:

.ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=Location.Column, Criteria1:=myValue

 

[suttorpa]

That progresses me a bit, it is actually detecting the code to autofilter, but it is giving me an error now on my variable.

Run-time error '424': Object Required

the myValue I am guessing needs to be declared as an object..

I just tried this Dim myValue As Object
But that gives me run-time error '91': Object variable or With block variable not set.

 

[mumps]

Try: Dim myValue as String

 

[suttorpa]

I changed to String, and checked the datavalue for the Location field (entire column set as "text").. but I continue to get Run-time error '424': Object required..

Location column is just port numbers.. 2-3 characters.. so 01D, 02D, 03D, some are a bit vague as SE for server room, or LA for label printing area.. Text values worked for me.

maybe there is a different way I can prompt for location and filter a catagory with input? I tried making and adjusting the commandText:
.CommandText = Array("SELECT * FROM [Query Entire Inventory] WHERE Location = myValue")

But that also gives 424 object required..

 

[mumps]

Instead of location.column, you should use the actual column number. For example, if the location is in column A, then use: Field:=1

 

[suttorpa]

I feel soo close.. yet so far.. Making that adjustment gives us a different error..

Run-Time Error '438':
Object doesn't support this property or method

here is my current code, debug stops at the red:

Sub Macro1()
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Dim myValue As String
myValue = InputBox("Give me some input")


Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Query Entire Inventory""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Query Entire Inventory]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Query_Entire_Inventory"
.Refresh BackgroundQuery:=False
.ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=1, Criteria1:=myValue
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[mumps]

The highlighted line of code is used to filter an Excel worksheet. Are you trying to filter an Excel sheet?

 

[suttorpa]

Yes, it is filtering a query, that pulls data from 5 other worksheets.. Each worksheet is an inventory item of a different type. The idea is to pull All items in a particular location into its own separate sheet.. each sheet has a Location column.

 

[suttorpa]

Basically.. all I want to do is create a query that pulls 4-5 matching columns from each worksheet based on user input for the Location column. I do not know of another way to do this.