I need help to merger 18 sheets of data ,all have the same kind of data only catch is that every sheet has a different header nomenclature

Eg i need data from 8 fields like
MMS id
Client group
SG
Client name
Opp Name
Market
Market unit
Submission data
Revenue
Lead SA
Support SA
NC parameter

The above would be my target headers . But in the 18 sheets i have headers are as below

ID - or Mm id
CG
sG
Master client name
MU
Markets
Proposal submission date or assignment date or deal date
Lead SAs
Support SAs or supporting sAs
Total revenue or ta+ sc revenue $m

I need the bna code to search the header n copy these columns and paste as per my target sheet header column and add the sheet name under NC parameter column


Any VBA code would be of great help in reducing manual work . Appreciate your help in this regards.

Thanks in advance