Insert Column after Column X, then use Vlookup on multiple workbooks

Long time Excel user, but new to Macros.

I’m trying to copy a set of date from Sheet1 to Sheet2, insert a column in Sheet2 after a column titled “Registered Number” and then in the new column in Sheet2 run a vlookup through multiple workbooks for the value in the Registered Number column.

The Registered Number column won’t always be in the same place, so I’ve managed to put together the code to find the column and insert a new column after this column and insert “Match” in R1 of that column.

This part works fine

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A1").CurrentRegion.Copy Destination:=Sheets("Sheet2").Range("A1")
Dim SearchText As String
Dim GCell As Range
SearchText = "Registered Number"
Set GCell = Worksheets("Sheet2").Cells.Find(SearchText).Offset(0, 1)
GCell.EntireColumn.Insert
Worksheets("Sheet2").Cells.Find(SearchText).Offset(, 1).Value = "Match"
End Sub

So for example in Sheet2 we should now have Columns A=Number, B = Description, C = Registered Number, D = Match and E = Spend

I want to insert a vlookup in whichever column Match is in (for this example D) which will match the value in Registered Number (Always the column before: for this one C) to one of 10 workbooks (There was too much data for a single table and my PC struggled with all the data in 1 workbook and 10 sheets).

The Registered Numbers start 00, 01, 02 etc. to 10 and the Workbooks are called “RN 000.xlsx”, “RN 010.xlsx” etc. etc. Also the Registered Numbers are Numbers stored as Text (To allow for the starting 0).



If possible I would like code to do the following;
In the Match (D) Column, run the vlookup depending on the first 2 digits of the Registered Number and continue to run this for each row containing a value in the Registered Number Column (C). The number of rows will also change so this will need to keep going until there is a "" value in Column C.

Thanks for your help