Macro to automatically rename row headers

Hi guys,

Where I work we use a database called Trent and sometimes we need to use a tool called Business Objects to run reports from this database. The trouble is that Trent uses headings that BO cannot deal with and I need a macro that can automatically rename the headers to something that BO can readily use to run reports.

I have attached two spreadsheets one called 'Column Heading Differences' and the other called 'Sample of Export from Business Objects'.

The first spreadsheet 'Column Heading Differences' this explains exactly what I need. Basically in row 9 this is what the extracted report names the headers, however I need the named headers as they appear in row 3.

Now if you look at the second spreadsheet entitled 'Sample of Export from Business Objects' this is how the extracted report looks like.

This second spreadsheet is the one where I need a Macro to change the field names in row 10 into something into names that BO can actually use.

It would be really helpful to automate this via a macro as it is quite time consuming to manually keep changing the names of the cells. Seeing as my VBA skills are pretty useless, i was wondering if some kind person would be able to write a small piece of code to help me with my problem.

Thanks in advance for any help given.

You need to make sure the Row & sheet references are correct, but something like this

Option Explicit

Sub changeheaders()
    With Sheet1
        .Cells(9, 1).Value = "Name"
        .Cells(9, 2).Value = "Position"
    End With
End Sub

Hi RoyUK,

Thank you so much this works perfectly. How can I make this macro available to all open worksheets so that I don't have to keep inserting modules each time I run this report?

Thanks.

Change With Sheet1 to With Activesheet & it will work on the currently open sheet.

Originally Posted by royUK

Change With Sheet1 to With Activesheet & it will work on the currently open sheet.

Sorry for the late feedback, Thanks for your help. What I wanted to know though is how can I make this Macro always available as soon as I open Excel? I am assuming it would have to be in the shape of an 'Add-In'?

If you o0nly need it on the one PC you can use PERSONAL.xls, to share it then an addin would be best.