Dynamic Macro, Self updating macro

[rafaelhok]

The issue: I have a macro that works based on the current sheet layout. However, if I add new column or rows, the macro will run but put the data in the old column and the rows referenced keep the old row number that was initially used. Hopefully the attached excel provides a clear message of what is going on.

I am trying to find a way to make the macro dynamic, that way if rows/columns get added/deleted, it will continue to run and pull data into the correct columns/rows.

Here is the macro
Sub Fill_Test()

Range("AL31").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-37]="""","""",IFERROR(XLOOKUP(RC[-37],'Data'!R9C3:R9C78,'Data'!R18C3:R18C78),""""))"

Selection.AutoFill Destination:=Range("AL31:AL211")
Range("AL31:AL211").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AL29").Select
End Sub


Any advice on this?

Also, how does one share an entire workbook?

Dynamic Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5
6
7
8
9			A	B	C	D	E	F	G	H	I	J	K
10
11
12
13
14
15
16
17
18			1	2	3	4	5	6	7	8	9	10	11
19
20
21
22
Data

Dynamic Macro.xlsm
	A																																			AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
22																																						forumla used in macro
23																																						=IF(A31="","",IFERROR(XLOOKUP(A31,Data!$C$9:$BZ$9,Data!$C$18:$BZ$18),""))
24
25
26
27																																									This was a recorded macro.
28																																									I first had the formula already copied to my clipboard.
29	Lookup Value																																					Data Value			I hit record, clicked on the cell AL31, pasted the formula in the formula bar, dragged the formula down, hit CTRL+C, right clicked and pasted as values, hit escape, clicked on another cell, and stopped recording.
30	Letters																																					Numbers
31	A																																					1
32	B																																					2			The issue:
33	C																																					3			when I add a new row or column (see next tab "Calcs (2)"), the macro does does not update to reflect the new changes.
34	D																																					4
35	E																																					5
36	F																																					6
37	G																																					7
38	H																																					8
39	I																																					9
40	J																																					10
41	K																																					11
42
43
44
Calcs

Dynamic Macro.xlsm
	A																																			AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
22																																							forumla used in macro
23																																							=IF(A31="","",IFERROR(XLOOKUP(A31,Data!$C$9:$BZ$9,Data!$C$18:$BZ$18),""))
24
25
26
27
28
29	Lookup Value																																				New column		Data Value
30	New row
31	Letters																																						Numbers
32	A																																					1	1
33	B																																					2	2			The issue:
34	C																																					3	3			now the macro runs but still puts the data in the AL column
35	D																																					4	4			and starts at row 31
36	E																																					5	5
37	F																																					6	6			Is there a way to write/manipulate the macro to update if a new row or column is insterted?
38	G																																					7	7
39	H																																					8	8
40	I																																					9	9
41	J																																					10	10
42	K																																					11	11
43
44
45
46
Calcs (2)

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic Macro, Auto update macro reference if adding/deleting rows/columns. [SOLVED]
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.