Excel Combobox to filter a querytable 1

[RomeERome]

Hello All,

I am currently trying to filter a querytable in Excel 2007 by using an activex combobox that is not on a userform.

Below is a copy of my code that is not working. I get an error that telling me I need an object, but when I create an object it still doesn't work.

Private Sub ComboBox1_Change()
    Dim Sheet As Object
    With Data
        Sheet.Range("Table_AP2_Accuracy.accdb").AutoFilter Field:=2 _
        , Criteria1:=ComboBox1.Value
    End With
End Sub

The syntax is what my issue is.

Your assistance is greatly appreciated.

 

[SkipVought]

hi,

Private Sub ComboBox1_Change()
    Dim Sheet As Object
    With Data  '[b][highlight]What is this object?[/highlight][/b]
'[b][highlight]Where have you SET this Sheet object???[/highlight][/b]
        Sheet.Range("Table_AP2_Accuracy.accdb").AutoFilter Field:=2 _
        , Criteria1:=ComboBox1.Value
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

Hi Skip,

Thanks for responding so quickly!!!

I guess the object is a worksheet called "Data".

So would I have to do the following?

Dim Sheet As Worksheet
Set Sheet = ActiveSheet("Data")

 

[SkipVought]

Private Sub ComboBox1_Change()
    Sheets("Data").Range("Table_AP2_Accuracy.accdb").AutoFilter Field:=2, Criteria1:=ComboBox1.Value
End Sub

This assumes that your Data sheet has a multi-cell range named Table_AP2_Accuracy.accdb

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

Wow Skip!!! That worked GREAT!!!

Thanks so much. I was close, but you got me on the right track.

 

[RomeERome]

Since this worked so well, here is a sample of my Excel VBA code.

Sheets("Data").Range("Table_AP2_Accuracy.accdb").AutoFilter Field:=2, Criteria1:=ComboBox1.Value
Sheets("Data").Range("Table_AP2_Accuracy.accdb").Copy
Sheets("Sheet1").Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, transpose:=True

Thanks again Skip!!!

 

[RomeERome]

Hello All/Skip,

Now that I have the first part of my code working, I want to copy a row of data from one worksheet, and paste it into another worksheet. The twist is that the worksheet is filtered, and I need to transpose the row data into a column, and paste it into a specific column.

Here is the code that I've written so far:

Sheets("AP2 Simulator").Range("D12:L12").Copy
Sheets("Sheet2").Range("Data1").AutoFilter Field:=2, Criteria1:=ComboBox1.Value
Sheets("Sheet2").Range("Data1").AutoFilter Field:=3, Criteria1:="*AP2*"
Sheets("Sheet2").Column("AP2 Simulator!B12").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, transpose:=True

The "AP2 Simulator!B12" entry is where I was trying to match a cell that had the same name as the column header that I am trying to paste the data into.

The filtering of the data is working great, but the pasting it into the column that I need is where I'm having trouble.

Your assistance is greatly appreciated.

 

[SkipVought]

I am totally confused!

You must be MUCH more descriptive about what you are trying to do. A picture is worth a thousand words -- post a COGENT axample.

This [highlight]Column("AP2 Simulator!B12")[/highlight] is crazy confusing!!! I have not idea what this is!!!

Help yourself out by making it perfectly clear!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

Hello Skip,

I thought I did, but I guess I was wrong. I have a cell on a worksheet called "AP2 Simulator". The cell has a concatenation of the year and the week. In the "Sheet2" worksheet, there is a column that corresponds to that same value, and I was trying to reference that data in the "AP2 Simulator" worksheet so that my code knew where to paste the data. That was my feable attempt at trying to match the data that I am trying to paste to the location that the data needs to be pasted to.

 

[SkipVought]

the location that the data needs to be pasted to

Descriptive?

Again, how about a cogent example -- ,copy n paste, from your sheet, please. Headings, please. Both source and target, please. Explain the logic, please.

Your code might know, but I sure do not!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

Skip did you not see my code, and my earlier descriptions?

Ok, here is what I trying to do from a visual perspective.

Week 8 Week 7 Week 6 Week 5 Week 4 Week 3 Week 2 Week 1
15,000 10,000 12,960 12,960 12,960 12,960 12,960 12,960

The data needs to look as follows:

Week 201146
Week 8 15,000
Week 7 10,000
Week 6 12,960
Week 5 12,960
Week 4 12,960
Week 3 12,960
Week 2 12,960
Week 1 12,960

The column labeled as Week is the first column in the Sheet2 spreadsheet. The column that is labeled 201146 could be anywhere in the data of the Sheet2 worksheet. It depends on how much data is being pulled from the database by the date range criteria.

I hope this helps.

 

[SkipVought]

what is the logic that describes where 201146 come from?

So you want to FIND "201146" in the heading row?

Based on the FOUND column, then PASTE in the first empty cell in that column?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

That's where the "AP2 Simulator!B12" comes from. I was trying to use that as a reference for comparison purposes. So that the paste could compare that to the column header in the Sheet2 worksheet.

The other twist is that the column is not empty, and it's filtered based on the filters that you see in my code below.

Sheets("Sheet2").Range("Data1").AutoFilter Field:=2, Criteria1:=ComboBox1.Value
Sheets("Sheet2").Range("Data1").AutoFilter Field:=3, Criteria1:="*AP2*"

 

[SkipVought]

Well, since you have not posted your table, your previously posted code is useless to me!

What is the LOGIC for determining what row the paste begins in for the found column?

You are making this Q & A VERY difficult!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

Skip,

I can't post the table data due to it being confidential so that's why I posted a snippet of the data so you can see what I trying to do. If I could post it I would post it.

I'm not trying to make it difficult.

Sheets("Sheet2").Column("AP2 Simulator!B12").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, transpose:=True

As I stated earlier, this was my feable attempt to paste the data to the column. As I stated earlier that the cell on the AP2 Simulator weeksheet, B12, is a concatenation of the current year and the current week which happens to be the column header in the Sheet2 worksheet.

 

[SkipVought]

[highlight]
What is the LOGIC for determining what row the paste begins in for the found column?[/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

I was thinking that the code would pull that from the "AP2 Simulator!B12" cell reference.

 

[SkipVought]

I thought that 201146 was in that cell?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

It is.

 

[RomeERome]

Remember that Sheet2 is filtered, and the starting row could be any row based on the filter this is being applied.