Loop with Index, Match

[JamesPa]

Hello!
I have a workbook which has up to 280 worksheets in it. I need to pull the data from one cell into a consolidated worksheet (named WIP in the attached file). I have the formula for the index and match: =INDEX(Sheet1!$P$1:$P$387,MATCH($G$1,Sheet1!$H$1:$H$387,0),1). I need a VBA which will loop through each of the worksheets and return the number identified by the formula. Can someone help me out with a suggestion?

 

[JamesPa]

Hopefully this helps with what I am wanting to do. The first pic is the WIP worksheet where I want to paste the data in the cell defined by the index match formula. The second is a pic of one of the worksheets with the data I need. Its the cell with 327 in it. I would like to code a loop to copy the data defined by the index match formula from each worksheet, then paste it into the WIP worksheet. Any assistance would be greatly appreciated.

 

[JamesPa]

Total doses test bed 102622.xlsx
	A	B	C	D	E
1
2
3	Day	Doses/day
4	1	327
5	2	2
6	3	7
7	4	2
8	5	2
9	6	2
WIP

Cell Formulas
Range	Formula
B4	B4	=INDEX(Sheet1!$P$1:$P$387,MATCH($G$1,Sheet1!$H$1:$H$387,0),1)
B5	B5	=INDEX(Sheet2!$P$1:$P$387,MATCH($G$1,Sheet2!$H$1:$H$387,0),1)
B6	B6	=INDEX(Sheet3!$P$1:$P$387,MATCH($G$1,Sheet3!$H$1:$H$387,0),1)
B7	B7	=INDEX(Sheet4!$P$1:$P$387,MATCH($G$1,Sheet4!$H$1:$H$387,0),1)
B8	B8	=INDEX(Sheet5!$P$1:$P$387,MATCH($G$1,Sheet5!$H$1:$H$387,0),1)
B9	B9	=INDEX(Sheet6!$P$1:$P$387,MATCH($G$1,Sheet6!$H$1:$H$387,0),1)

Total doses test bed 102622.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
94								10:20:25 AM	1	Phase 3-1 (2 day dosing s	63	0	0	2	3	4	252			3078
95								9:20:29 AM	1	Phase 2-2 (3 day dosing	120	0	0	2	1	2	240			2590
96								10:20:22 AM	1	Phase 3-1 (2 day dosing s	120	0	0	3	2	3	360			2590
97								7:18:25 AM	1	Phase 4 - 13 Carries	100	0	0	2	0	1	100			2590
98								6:39:35 AM	1	Phase 2-1 (4 day dosing s	165	0	0	2	0	1	165			3078
99								Totals by Drug/Dose Type:				0	0	334	230	327	25497
100
101
102
Sheet1

Here are the mini sheets for my project. I have the formulas in the WIP worksheet which identifies the correct cell in the other worksheets (cell P990 in the mini sheet I uploaded). I am very new to coding so any assistance on a VBA which will loop through all worksheets, other than the WIP worksheet, copy the cell identified by the formula, then paste it in the WIP worksheet will be greatly appreciated.