0

I am trying to get a database from Workbook B to autofilter using some input from Workbook A. I am building the macro in Workbook A.

I have the following: Workbook A - Document were you start working Worrkbook B - Database, the final objective of this code is to import some info from Workbook B to Workbook A

I need the following:

  • By double clicking in a column from workbook A, workbook B should open (done)
  • The clicked value from workbook A will be saved as a variable, lets call it input_db (done)
  • Workbook B will autofilter based on input_db (not done, help required here!)
  • The required data is selected from workbook B and imported to workbook A, preferably with a double click as well (not done yet, but if you have any suggestion for this, Ill be gratefull ;))

The process should be repeated several times in a row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim input_row, input_column As Integer
Dim input_db As String
Dim wbB, wbA As Workbooks

If Not (Application.Intersect(ActiveCell, [Links]) Is Nothing) Then 'Links is the name of the range where I need the code to be active
If ActiveCell.Value <> "" Then

input_row = ActiveCell.row
input_column = ActiveCell.Column
input_db = ActiveCell.Value

Set database = Workbooks.Open("Workbook B location")


ActiveWorkbook.ActiveSheet.Range("A9").AutoFilter Field:=1, Criteria:=input_db ' This bit doesnt work as expected


End If
End If

End Sub
DFSK
  • 3
  • 1

1 Answers1

0

As I can see you are using the wrong format of Autofilter. Try this line after amending as per your need. Autofilter takes in Criteria1 not Criteria.

ActiveWorkbook.ActiveSheet.Range("A9").AutoFilter Field:=1, Criteria1:=input_db

Also you can find more about Autofilter here: Link

Let us know if it still doesn't work.

Edit 2 :

You will have to open a input box and select the cell. You can follow the This Link for that.

Mikku
  • 6,538
  • 3
  • 15
  • 38
  • Hi Mohit, Thanks a lot for correcting such a dumb mistake from my side =) For the second part I want to select only one item from the filtered list, not the whole range. Basically, Imagine you are making a Football team, you click on goalkeepers (workbook A) and worbook B will open and give you a list of available GK. From there you have to select one, and that selection should be pasted and showed in Workbook A. – DFSK May 22 '19 at 11:51
  • Sure... I have added a link in the answer for you to follow. Please mark the answer as accepted. :) – Mikku May 22 '19 at 14:04