Bin has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Access Intermediate course
Access Advanced course
Excel VBA Advanced course
Excl VBA rename and reorganise header
Hi I have got some row data and i want to rename them. Is there any macro can do so? Full details please see below.
Thanks Bin
I have the header from the rawdata the same as the "RawData" title line in below. I want to replace all of them with the ones in the "New Title" line.
Column title: A, B, C, D, E, F, G, H, I, J, K, L, M
RawData title: Cy, City, Supplier, City, Date, Site/Bkg, Agent, Nat, Tyrpe Tpye, Resp., Reason, /Loss ST, Remarks
New Title: Country Code, Supp City, Supplier, Svc.City, Arri Date, Tour Ref.Site/Bkg, Agent, Nat, Comp Type, Resp., Reason, Profit/Loss Status, Remarks
RE: 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
