Dynamic Index Match

[kj1]

Okay so I am stuck trying to create this macro that will be able to quickly autofill the columns needed. I have data on multiple workbooks inside the same worksheet and I am trying to combine certain columns from each worksheet into one. The problem that I am running into is how I can make the ranges dynamic since the data will change. The code below works for a static range, but will not be useful without it being dynamic.

I want the "Master" sheet to look like the "After Macro" sheet when it is all complete. Any help would be greatly appreciated!



Dim Last As Double
With ActiveSheet
Last = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
Range("C2").Formula = _
"=INDEX(Data!R2C2:R13C2,MATCH(Master!R2C2,Data!R2C1:R13C1,0))"
Range("C2").AutoFill Destination:=Range("C2:C" & Last)

"Master" Sheet

Test.xlsx
	A	B	C	D	E	F	G
1	Month	Job #	Name	Status	Type	Date	Total
2	October	Job 1
3	October	Job 2
4	October	Job 3
5	October	Job 4
6	October	Job 5
7	October	Job 6
8	October	Job 7
9	October	Job 8
10	October	Job 9
11	October	Job 10
12	October	Job 11
13	October	Job 12
Master

"Data" Sheet

Test.xlsx
	A	B	C	D	E
1	Job #	Name	Type	Date	Zip
2	Job 1	AAA	Clean	01/12/19	45644
3	Job 2	BBB	Repair	01/01/20	56448
4	Job 3	CCC	Fix	08/19/20	58476
5	Job 4	DDD	Warranty	07/05/20	96548
6	Job 5	EEE	Fix	03/30/20	76485
7	Job 6	FFF	Clean	04/11/20	48575
8	Job 7	GGG	Warranty	06/20/19	48756
9	Job 8	HHH	Fix	04/22/20	48785
10	Job 9	III	Warranty	05/14/20	48888
11	Job 10	JJJ	Repair	05/14/20	45721
12	Job 11	KKK	Repair	02/02/20	64852
13	Job 12	LLL	Repair	09/29/20	67549
Data

"Status" Sheet

Test.xlsx
	A	B	C	D
1	Job #	Status	Code	Task
2	Job 1	Complete	1	Call
3	Job 2	Complete	5	Call
4	Job 3	In Progress	77	Follow Up
5	Job 4	Complete	44	Call
6	Job 5	In Progress	7	Call
7	Job 6	In Progress	8	Follow Up
8	Job 7	In Progress	6	Follow Up
9	Job 8	In Progress	5	Follow Up
10	Job 9	In Progress	4	Follow Up
11	Job 10	In Progress	58	Call
12	Job 11	In Progress	45	Call
13	Job 12	Complete	60	Follow Up
Status

"Cost" Sheet

Test.xlsx
	A	B	C
1	Job #	Trips	Total
2	Job 1	5	$ 1,200.00
3	Job 2	2	$ 4,658.00
4	Job 3	15	$ (9,857.00)
5	Job 4	2	$ 657.00
6	Job 5	65	$ 945,756.00
7	Job 6	40	$ 20,405.00
8	Job 7	42	$ 68,519.00
9	Job 8	44	$ 68,741.00
10	Job 9	31	$ (1,853.00)
11	Job 10	1	$ 197.00
12	Job 11	59	$ 304,985.00
13	Job 12	10	$ 60,721.00
Cost

"After Macro" Sheet

Test.xlsx
	A	B	C	D	E	F	G
1	Month	Job #	Name	Status	Type	Date	Total
2	October	Job 1	AAA	Complete	Clean	01/12/19	$ 1,200.00
3	October	Job 2	BBB	Complete	Repair	01/01/20	$ 4,658.00
4	October	Job 3	CCC	In Progress	Fix	08/19/20	$ (9,857.00)
5	October	Job 4	DDD	Complete	Warranty	07/05/20	$ 657.00
6	October	Job 5	EEE	In Progress	Fix	03/30/20	$ 945,756.00
7	October	Job 6	FFF	In Progress	Clean	04/11/20	$ 20,405.00
8	October	Job 7	GGG	In Progress	Warranty	06/20/19	$ 68,519.00
9	October	Job 8	HHH	In Progress	Fix	04/22/20	$ 68,741.00
10	October	Job 9	III	In Progress	Warranty	05/14/20	$ (1,853.00)
11	October	Job 10	JJJ	In Progress	Repair	05/14/20	$ 197.00
12	October	Job 11	KKK	In Progress	Repair	02/02/20	$ 304,985.00
13	October	Job 12	LLL	Complete	Repair	09/29/20	$ 60,721.00
After Macro

Cell Formulas
Range	Formula
C2:C13	C2	=INDEX(Data!$B$2:$B$13, MATCH(Master!$B2,Data!$A$2:$A$13,0))
D2:D13	D2	=INDEX(Status!$B$2:$B$13,MATCH(Master!$B2,Status!$A$2:$A$13,0))
E2:E13	E2	=INDEX(Data!$C$2:$C$13, MATCH(Master!$B2,Data!$A$2:$A$13,0))
F2:F13	F2	=INDEX(Data!$D$2:$D$13, MATCH(Master!$B2,Data!$A$2:$A$13,0))
G2:G13	G2	=INDEX(Cost!$C$2:$C$13,MATCH(Master!$B2,Cost!$A$2:$A$13,0))

 

[JGordon11]

Change "=INDEX(Data!R2C2:R13C2,MATCH(Master!R2C2,Data!R2C1:R13C1,0))"

to

"=INDEX(Data!R2C2:R" & Last & "C2,MATCH(Master!R2C2,Data!R2C1:R" & Last & "C1,0))"

 

[kj1]

Thank you! I had tried a similar option earlier, but I had the "" in the wrong places which was giving me compile errors. Your method works great!