find partial matches in header of all worksheets and replace with set values

[albytross]

Hi,
My background is CSS and HTML (yes, i'm a fossil).

Can anyone help with some code? I'm impressed how much i've managed to get done using various forums but now I am well and trully stuck.

I have a workbook open with numerous worksheets (the total number of worksheets changes, depending on the data dump I receive)
The worksheets have similar but different header values despite referencing comparable data, and I want to make the column names uniform (so later I can easily append/merge the data)

I'm searching for some code which:
- References a user-defined list of header values contained on a master-sheet
- searches the other open worksheet for "similar" header values to those in the list, based on either partial matches or matches of multiple criteria, and then renames the cell to the list value

e.g. user defined list contains "car" "fuel" "kilometers" - but my worksheets contain values such as "Car_1", "Car_MG_2" "Car_Drive" "Fuel" "fuel_1" "kilometers_2" "kilometers_odo".
Anything with "car**" should rename to "car", and so on.

my data doesnt relate to cars but it seemed an easy example.

Alby

 

[Alex Blakenburg]

I want to make the column names uniform (so later I can easily append/merge the data)

Since the goal is to append/merge the data anyway, have you considered using Power Query for the exercise ?