VBA Code to copy cell value from multiple workbooks with drop-down lists

Qbiaczeq

New Member
Joined
May 17, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Dear Forum Members,

I would like to post the following question:

As part of our process, I need to extract data from specific cells located in different Workbooks (each has a different nomenclature, structure, form).
The individual data is placed in tables, filtered with a drop-down list.

Example:

I need a monthly summary report of John Smith in one place (Utilization, Evaluation, Sales).
Utilization is located in Workbook1
Evaluation in Workbook2
Sales in Workbook3

In each of the Workbooks there is a table with a drop down list that allows you to filter the data of a specific employee (John Smith, Chris Carter, Aaron Rodgers).

Is there a way to extract data relating to John Smith from these 3 places and consolidate into one table?

At the moment, it is done manually for each employee separately, and combined together in one table for the management, it involves burdensome searching and filtering out information in many files. I would like to automate this process thanks to VBA, however, I have only received beginners training in this topic and I do not know exactly where to start.

For starters, I wanted to get the information from a single source only to get a point of reference, but I was overcome by the drop-down list problem.

TLDR: I would like to extract information about John Smith from the file Workbook1 (B4: B9) and put it in Workbook2 (B3: G3). The difficulty is that in Workbook1 there is a drop-down list, and when Chris Carter is selected, the values in cells B4: B9 change accordingly. Is it possible to adapt the VBA code so that it distinguishes between John Smith and Chris Carter? This kind of a dashboard is being created for every employee in our department, and we would like to automate the process.

Thank you very much in advance for your answers.

P.S. Apologies if anything is not stated clearly enough, English is not my native language.
_________________
Best wishes
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Qbiaczeq

New Member
Joined
May 17, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Sorry, this is my first post on any forum in my life. My intention is not for anyone to take this as a request to do the work for me.

Attached are 2 files (Test 1, Test 2). I deleted all unnecessary columns, charts, tables and confidential data.

In summary, the goal is to write a code that would transfer data assigned to a specific person based on a drop-down list from the Test2_Monthly Individual (H4: H14) file to Test1_Dashboard (I9: AF9).
 

Attachments

  • Test1.png
    Test1.png
    88.1 KB · Views: 5
  • Test2.png
    Test2.png
    49.1 KB · Views: 5

Watch MrExcel Video

Forum statistics

Threads
1,133,827
Messages
5,661,147
Members
418,619
Latest member
ibruzzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top