Function or Macro to Clean Up Column Headings

[Alex20850]

I have a spreadsheet with several dozen tabs. Each tab is in a format for converting into databases.
I will be using a program to convert them into Oracle SQL insert scripts.
Currently, some of the column headings start with numbers and have spaces, for example 2010 land area.
It should be changed to YR2010_land_area. I am assuming if it starts with a number it is a year.
This is because Oracle SQL columns cannot start with numbers or have spaces.
I am not a VBA programmer, but I have installed function and macro code before, but I don't what would work better in this case.

 

[Fluff]

Are the headers rows on each tab on row 1 & are they all the same number of columns?
If they are the same number of columns how many?

 

[Alex20850]

Are the headers rows on each tab on row 1 & are they all the same number of columns?
If they are the same number of columns how many?

The headers are always in row 1. The number of columns varies.

 

[Fluff]

Ok, how about

Sub Alex()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Ws.Range("1:1").Replace " ", "_", xlPart, , , , False, False
      With Ws.Range("A1", Ws.Cells(1, Columns.Count).End(xlToLeft))
         .Value = Evaluate(Replace("if(iserror(left(@)+0),@,""YR""&@)", "@", .Address))
      End With
   Next Ws
End Sub

 

[Alex20850]

The macro didn't work and I would like to use a function instead.
I would create a new row and then run the function on the column headings.

I ran the macro on a page:
The column headings:

State Abbreviation

State FIPS Code

County FIPS Code

FIPS Entity Code

ANSI Code

Full Name

GU Name

Entity Description

After the macro:

0​

0

0

0

0

0

0

0​

 

[Fluff]

The macro didn't work

In what way?

 

[Alex20850]

In what way?

State Abbreviation	State FIPS Code	County FIPS Code	FIPS Entity Code	ANSI Code	Full Name	GU Name	Entity Description
Should be:
State_Abbreviation	State_FIPS_Code	County_FIPS_Code	etc.

 

[Fluff]

But what does the macro give you?

 

[Alex20850]

The macro didn't work and I would like to use a function instead.
I would create a new row and then run the function on the column headings.

I ran the macro on a page:
The column headings:

State Abbreviation

State FIPS Code

County FIPS Code

FIPS Entity Code

ANSI Code

Full Name

GU Name

Entity Description

These are the results:

0

0

0

0

0

0

0

0

 

[Fluff]

I am not going to spend time writing a function until you till me in what way the macro failed. Otherwise the function is likely to fail as well.