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

[urbanyeti]

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

[dflak]

You can solve some of your problem by organizing your data into an Excel Table. Excel tables do thing by Column Header name and not by column position. Also Excel Tables automatically copy down formulas, so if you add the formula to a single cell in a newly-created column, it will populate automatically to all rows in that column. That's about as far as I can take the advice without actually seeing a workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.