Excl VBA rename and reorganise header

Hi Bin

There are a number of ways to replace the headings. One is to use a macro that uses Data, Text to Columns and another is to set up a Custom List. The second way doesn't involve VBA.

Here is the Text to column macro that works when the active cell in on the first cell containing te heading Cy.

Sub ReplaceHeadings()
Dim StartCell As String
StartCell = ActiveCell.Address

Application.DisplayAlerts = False 'stops the prompt at the end

ActiveCell = "Country Code, Supp City, Supplier, Svc.City, Arri Date, Tour Ref.Site/Bkg, Agent, Nat, Comp Type, Resp., Reason, Profit/Loss Status, Remarks"

Selection.TextToColumns Destination:=Range(StartCell), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
)), TrailingMinusNumbers:=True

Application.DisplayAlerts = True

End Sub


The second method is to highlight all the new headings (Country Code, Supp City etc)
Select File, Options, Advanced
Click Edit Custom Lists from General section
Click Import
OK

Escape back to your worksheet and now you can type Country Code and use the Autofill handle to create all the headings automatically.
In the same way that you create Jan, Feb, Mar with Autofill this is a permanent setting for Excel.

Use the macro if part of another macro otherwise Autofill may be more practical.

Hope that helps
Regards
Doug
Best STL