VBA to Vlookup from pivot tables diffrent files

[aaleem]

Hi,

i have two files (1. the current file and 2. Last week's file) where Pivot tables are there on multiple sheets. I m trying to bring the last week's data into the current file. The issue I'm facing is the same status is available in the Comb. Status column.

is there a possibility to bring the last week's data by combining two columns (Col A and Col B) from the pivot tables for each of the statuses on all the sheets in the current file thru VBA .

I'm attaching a sample Data, there will be two separate files and the name will get updated each week.

Any help will be appreciated.

Book2
	A	B	C	D	E	F	G
1			Values		Last Week
2	Remarks	Comb.Status	Count of GRN Number	Sum of Converted AED	GRN No.	AED
3	Not Yet Due	Awaiting Appointment	12	377,993	45	618,151	Vlookup (ColA &ColB, from last week file, Count of GRN Number)
4		Awaiting Manual Match	580	1,638,981	1290	3,055,123
5		Awaiting REIM Posting	50	1,042,842	22.5	388,531	The Table Array is not fixed, this will be +/- rows in the last week file
6		Awaiting Shipment Recipt Warehouse	31	395,082	187.5	2,086,640
7		Awaiting System Match	18	372,969	47.5	1,340,514
8		Awaitng FSS submission warehouse	314	3,291,477	537.5	4,158,195
9		Failed to Post REIM	2	44,805	7.5	81,719
10		Pending Liability Creation	83	672,599	977.5	4,179,833
11		Pending with Planner	670	8,705,728	1715	15,095,803
12		Pending with Supply Chain	18	387,994	40	677,422
13		Awaiting EDI File Creation	14	361,170	10	234,169
14		Pending with Warehouse-GRN showing in Microstrategy/No Handles	454	4,745,311	447.5	1,283,872
15		Pending with Warehouse-GAC	45	198,071	55	125,119
16		Pending with Stores- Drag and drop not done/Handle not created	156	2,828,927	252.5	2,644,851
17		Manual Match-DOC in folder/Handle not created	12	111,815	37.5	203,455
18		Pending QA-DSD	309	2,056,889	1515	12,441,923
19		Pending with Warehouse-GRN showing in Microstrategy/Handle created	6	105,842	25	216,871
20	Not Yet Due Total		2,774	27,338,495	7,213	48,832,191
21	Over Due	Awaiting Manual Match	64	253,723	198	360,711
22		Awaiting REIM Posting	8	61,276	23	108,147
23		Awaiting Shipment Recipt Warehouse	1	939	3	1,601
24		Awaitng FSS submission warehouse	39	261,641	85	707,916
25		Failed to Post REIM	10	41,673	3	3,215
26		Pending Liability Creation	97	1,063,445	413	2,562,483
27		Pending with Planner	460	6,124,688	1,155	11,125,584
28		Awaiting EDI File Creation	1	21,222	5	6,088
29		Pending with Warehouse-GRN showing in Microstrategy/No Handles	19	192,012	63	344,632
30		Pending with Stores- Drag and drop not done/Handle not created	2	11,043	18	43,420
31		Pending with Warehouse-GRN showing in Microstrategy/Handle created	33	252,449	78	393,555
32	Over Due Total		734	8,284,111	2,040	15,657,352
33	(blank)	Awaiting Manual Match	7	64,797
34		Awaitng FSS submission warehouse	2	52,680
35		Pending QA-DSD	2	50,574
36		Pending with Warehouse-GRN showing in Microstrategy/Handle created	1	309
37	(blank) Total		12	168,360
38	Due in 7 Days	Awaiting Manual Match	40	99,878
39		Awaitng FSS submission warehouse	11	144,607
40		Failed to Post REIM	1	29,432
41		Pending Liability Creation	7	7,576
42		Pending with Planner	93	888,529
43		Pending with Warehouse-GRN showing in Microstrategy/No Handles	2	482
44		Pending with Stores- Drag and drop not done/Handle not created	1	614
45		Pending with Warehouse-GRN showing in Microstrategy/Handle created	7	95,457
46	Due in 7 Days Total		162	1,266,576
47	Grand Total		3,682	37,057,542
48
Current File

Cell Formulas
Range	Formula
E3:E19	E3	=VLOOKUP(B3,'Last Week file'!$B$5:$D$21,2,0)
F3:F19	F3	=VLOOKUP(B3,'Last Week file'!$B$5:$D$21,3,0)
E20:F20	E20	=SUM(E3:E19)
E21:E31	E21	=VLOOKUP(B21,'Last Week file'!$B$23:$D$36,2,0)
F21:F31	F21	=VLOOKUP(B21,'Last Week file'!$B$23:$D$36,3,0)
E32:F32	E32	=SUM(E21:E31)

Last week file

Book2
	A	B	C	D	E
1
2
3			Values
4	Remarks	Comb.Status	Count of GRN Number	Sum of Converted AED
5	Not Yet Due	Awaiting Appointment	45	618,150.61
6		Awaiting Manual Match	1290	3,055,123.34
7		Awaiting REIM Posting	22.5	388,530.69
8		Awaiting Shipment Recipt Warehouse	187.5	2,086,640.10
9		Awaiting System Match	47.5	1,340,513.52
10		Awaitng FSS submission warehouse	537.5	4,158,195.12
11		Failed to Post REIM	7.5	81,718.79
12		Pending Liability Creation	977.5	4,179,833.46
13		Pending with Planner	1715	15,095,803.41
14		Pending with Supply Chain	40	677,422.46
15		Awaiting EDI File Creation	10	234,168.78
16		Pending with Warehouse-GRN showing in Microstrategy/No Handles	447.5	1,283,872.19
17		Pending with Warehouse-GAC	55	125,118.95
18		Pending with Stores- Drag and drop not done/Handle not created	252.5	2,644,850.83
19		Manual Match-DOC in folder/Handle not created	37.5	203,454.74
20		Pending QA-DSD	1515	12,441,922.66
21		Pending with Warehouse-GRN showing in Microstrategy/Handle created	25	216,871.17
22	Not Yet Due Total		7212.5	48,832,190.83
23	Over Due	Awaiting Appointment	2.5	41,988.51
24		Awaiting Manual Match	197.5	360,711.26
25		Awaiting REIM Posting	22.5	108,147.40
26		Awaiting Shipment Recipt Warehouse	2.5	1,600.91
27		Awaiting System Match	10	220,752.09
28		Awaitng FSS submission warehouse	85	707,915.77
29		Failed to Post REIM	2.5	3,215.23
30		Pending Liability Creation	412.5	2,562,482.66
31		Pending with Planner	1155	11,125,583.83
32		Pending with Supply Chain	22.5	93,956.38
33		Awaiting EDI File Creation	5	6,087.78
34		Pending with Warehouse-GRN showing in Microstrategy/No Handles	62.5	344,631.75
35		Pending with Stores- Drag and drop not done/Handle not created	17.5	43,420.38
36		Pending with Warehouse-GRN showing in Microstrategy/Handle created	77.5	393,554.85
37	Over Due Total		2075	16,014,048.80
38	(blank)	Awaiting Manual Match	10	21,708.21
39		Awaiting Shipment Recipt Warehouse	2.5	82,785.70
40		Pending Liability Creation	5	156,998.16
41		Pending with Planner	2.5	39,753.19
42		Pending with Supply Chain	2.5	7,056.53
43		Awaiting EDI File Creation	2.5	795.54
44		Pending with Warehouse-GRN showing in Microstrategy/Handle created	2.5	526.21
45	(blank) Total		27.5	309,623.53
46	Due in 7 Days	Awaiting Manual Match	105	325,208.51
47		Awaiting Shipment Recipt Warehouse	10	180,630.14
48		Awaiting System Match	2.5	2,989.39
49		Awaitng FSS submission warehouse	20	152,456.84
50		Pending Liability Creation	22.5	135,185.39
51		Pending with Planner	157.5	1,525,240.29
52		Pending with Stores- Drag and drop not done/Handle not created	5	20,650.46
53	Due in 7 Days Total		322.5	2,342,361.02
54	Grand Total		9637.5	67,498,224.19
55
Last Week file

thanks
aleem

 

[aaleem]

Hi,
I tried linking with the last week's file by selecting the GET Pivot Data data and replacing it with the cell refernce. the problem I'm getting is when we send the file to another user all the last week's data is showing Ref. Error.

is there any possibility we can get this result via VBA and when we send the file to another user the reference error should not come?