use a lookup table within my vba code?

[Guitarmageddon]

So I have a simple vba to reformat a report I use constantly. Its pretty simple, and the report Im reformatting only has one tab.

I want to add onto that code, and basically insert a column which is a vlookup from a store number, to its parent warehouse.This means Im inserting a tab, pasting the lookup data, and just doing a vlook.

However, Im confused on how I can make that vlookup reference table "live" within my vba. Can anyone assist? I hope this makes sense.

So here is what Ive got. This is me inserting the column, and renaming it "parent rdc", followed by me inserting the tab "rdc lookup" that I presumably needed to paste the lookup data for the vba to use. Then, when I want to do the vlookup, the first chunk of commented steps is me pulling the vlookup data from some other sheet. This is basically where I want to make that vlookup data reside within the VBA code. Is that possible?

The second comment is me trying to remember what the dynamic autofill vba was, versus a defined range (since my report length is always different.

    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "PARENT RDC"
    Range("O11").Select
    Columns("N:N").ColumnWidth = 8
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "RDC LOOKUP"
    
    'Windows("Daily AR Orders 2019.xlsx").Activate
    'Range("A1:C1").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Copy
    'Windows("Sales by Style Report.xlsx").Activate
    'Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        'SkipBlanks:=False, Transpose:=False
    
    ActiveSheet.Paste
    Sheets("Page1").Select
    Range("N9").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'RDC LOOKUP'!C1:C3,3,0)"
    'THE NEXT STEPS NEED TO GET CHANGED TO THE AUTOFILL TO END OF DATA
    Range("N9").Select
    Selection.AutoFill Destination:=Range("N9:N279")
    Range("N9:N279").Select

The data that I am trying to make vba use as lookup values is here.

Spoiler: lookup values

Site	CMD	PARENT RDC
01100	HHM	60001
02100	QUM	60001
02309	QUM	60001
04100	CPM	60001
05100	CLM	60001
09101	SDM	70001
10101	PNM	70001
11100	MRM	70001
13100	TWM	70001
14100	YUM	70001
18100	SCM	60001
05102	CLM	60001
05300	CLM	60001
09100	SDM	70001
15100	KBM	70001
16100	IWM	70001
02212	QUM	60001
05201	CLM	60001
05202	CLM	60001
05207	CLM	60001
05233	CLM	60001
05315	CLM	60001
10252	PNM	70001
02202	QUM	60001
02211	QUM	60001
03100	ELM	60001
05206	CLM	60001
08100	ALM	60001
10113	PNM	70001
10230	PNM	70001
10251	PNM	70001
10321	PNM	70001
10343	PNM	70001
13307	TWM	70001
15101	KBM	70001
18101	SCM	60001
05301	CLM	60001
10320	PNM	70001
10332	PNM	70001
15305	KBM	70001
16001	IWM	70001
02214	QUM	60001
05305	CLM	60001
09700	SDM	70001
16300	IWM	70001
02301	QUM	60001
02305	QUM	60001
02500	QUM	60001
09500	SDM	70001
10001	PNM	70001
11300	MRM	70001
05211	CLM	60001
04200	CPM	60001
13379	TWM	70001
15303	KBM	70001
01200	HHM	60001
02304	QUM	60001
04300	CPM	60001
10314	PNM	70001
10352	PNM	70001
10353	PNM	70001
10362	PNM	70001
11200	MRM	70001
15301	KBM	70001
15400	KBM	70001
05309	CLM	60001
09300	SDM	70001
13305	TWM	70001
13314	TWM	70001
05310	CLM	60001
05311	CLM	60001
05314	CLM	60001
05316	CLM	60001
12100	BAM	70001
13306	TWM	70001
14400	YUM	70001
02205	QUM	60001
02302	QUM	60001
02306	QUM	60001
02307	QUM	60001
04400	CPM	60001
05209	CLM	60001
05302	CLM	60001
05303	CLM	60001
05304	CLM	60001
05306	CLM	60001
09301	SDM	70001
10303	PNM	70001
10304	PNM	70001
10322	PNM	70001
10323	PNM	70001
10324	PNM	70001
10325	PNM	70001
10326	PNM	70001
10331	PNM	70001
10333	PNM	70001
10341	PNM	70001
10342	PNM	70001
10344	PNM	70001
10413	PNM	70001
10451	PNM	70001
11301	MRM	70001
13303	TWM	70001
14301	YUM	70001
05001	CLM	60001
05500	CLM	60001
15001	KBM	70001
13380	TWM	70001
01500	HHM	60001
04500	CPM	60001
03400	ELM	60001
10229	PNM	70001
13101	TWM	70001
15200	KBM	70001
16107	IWM	70001
18301	SCM	60001
18703	SCM	60001
18302	SCM	60001
09103	SDM	70001
13001	TWM	70001
12300	BAM	70001
10118	PNM	70001
10500	PNM	70001
05200	CLM	60001
10364	PNM	70001
16200	IWM	70001
12301	BAM	70001
16301	IWM	70001
10003	PNM	70001
02802	QUM	60001
02801	QUM	60001
60001	EC STOW	60001
70001	WC STOW	70001
16101	IWM	70001

This is the report that I am attempting to vlook the data into. Column N will reference the existing "SITE" column M, and use the spoiler data I provided above. I have already executed the vlookup below in N as it should end up playing out. This is like a .5 second vlookup to do in normal excel, I just dont get how to make the VBA keep the reference table within itself. Let me know if I can clarify anything!

 

[Fluff]

Glad you sorted it & thanks for the feedback