Is thereVBA/Macro to Extract New Data from Access to Excel?

[n_lindsey]

Hello.

I am in a bit of a crisis. I am fairly new to VBA/macros and seek assistance if possible. I created a form in Access to capture specific data. I created a query from the table for which this data is stored. The query is refreshed in an Excel workbook with an OLEDB connection.

The excel workbook contains several summary sheets that pull the OLEDB data using MATCH/INDEX functions and other formulas for calculations.

My problem is, outside of the Access form entries, I also have to manually enter all new data in 4 cells of each summary sheet for each record added to Access so the MATCH/INDEX function can work. This can be quite tedious depending on # of recs entered in Access and the goal is always to reduce entry time by automation.

Is there a way I can connect the data fields needed from the Access form to auto-populate in Excel or some kind of VBA/Macro code the can recognize and extract new data refreshed from OLEDB to auto-populate for specific columns such as:
• Series #
• Date
• Company
• Investment

Keep in mind there are about 6 Summary sheets that would need to be populated with the same data but in different areas of each spreadsheet depending on what category is being sorted.
I have attached a sample workbook that gives a very light version of what I am working with less the number of summary sheets.

I hope this makes sense and is not too complex of an ask for assistance.


Thank you in advance.
Nickel.