PDA

View Full Version : Linking Content of Wkbk1 to Wkbk2, Extract filtered information into table in wkbk2



becksp
02-03-2016, 02:00 PM
So I have some excel VBA experience but am at a loss on how to code this... (have a vision, but not the advanced expertise)

INFO:
I have a workbook (call it WB1) filled with data (10,000+ lines). This workbook is updated to a shared drive, monthly.
side note: As of now, WB1 contains a pivot table to allow me to filter the workbook by my name and determine my month's totals.
*Each Month, I save this workbook to my computer as filename:= "C:\Documents\Monthly Reports\" & "2016" & "\" & nameofWB1 & ".xlsx"
Data tab = "datafull"
Pivot Table Tab = "pivotdata"

I have created a second workbook (call it WB2). This workbook contains my monthly trend charts from data I collect from WB1. I currently manually copy/paste my data from the pivot table into WB2, after filtering appropriately.


I want to automate this process. i want to automatically filter the data in WB1 by my name and customer (for any document saved) so that I can get customer monthly trends as well. My plan is as follows, but i have no idea how to do it - or if it is possible

(1) I need to link WB1 to WB2 when WB1 is closed.
in WB2 (Chart Workbook) I WILL INPUT:
Cell D2 = DesiredMonth
D3 = SavedFileNameofWB1
D4 = MyNameforFilter
D5 = Customer Name for Filter

(2) Filter Column N in WB1 (Data Workbook) to equal cell D4 (my name). AND
(3) Filter Column B in WB1 to equal cell D5 (Customer Name)
(4) Filter Column O in WB1 to equal D2 (Desired Month)
(5) With those filters in place, SUM Column AA (by name, customer, and month)
(6) input sum to a chart in WB2 (D122 = Jan, D123 = Feb, D124 = Mar, etc)

SO: all items sold by me, during January, for customer X will be summed from WB1 and placed into WB2 cell D122. Same with Februay, March, Etc.




Any help is much appreciated.

Thank you in advanced!