Column Headings Repeating

[Bob]

I have the following code which changes column headings. I'd like it to
repeat over the 8 sheets I have. I think I'm missing a "Next" statement but
not sure where.

Thanks.

Sub namefixer()
Dim X As Long
Dim Sh As Variant
For Each Sh In Array("BOOK_PrecBallMatches", "SHIP_PrecBallMatches", _
"Book_Lin_Bearing_Matches", "Ship_Lin_Bearing_Matches",
"Book_ProfRailMatches", _
"Ship_ProfRail_Matches", "Book_60_CaseMatches", "Ship_60Case_Matches")
s = Array("CustType", "Duns", "Name", "Jan-2004", "Feb-2004", _
"Mar-2004", "Apr-2004", "May-2004", "June-2004", "July-2004", _
"Aug-2004", "Sept-2004", "Oct-2004", "Nov-2004", "Dec-2004", _
"Jan-2005", "Feb-2005", "Mar-2005", "Apr-2005", "May-2005", _
"June-2005", "July-2005", "Aug-2005", "Sept-2005", "Oct-2005", _
"Nov-2005", "Dec-2005", "Jan-2006", "Feb-2006", "Mar-2006", _
"Apr-2006", "May-2006", "June-2006", "July-2006", "Aug-2006", _
"Sept-2006", "Oct-2006", "Nov-2006", "Dec-2006", "Jan-2007", _
"Feb-2007", "Mar-2007", "Apr-2007", "May-2007", "June-2007", _
"July-2007", "Aug-2007", "Sept-2007", "Oct-2007", "Nov-2007", _
"Dec-2007", "Jan-2008", "Feb-2008", "Mar-2008", "Apr-2008", _
"May-2008", "June-2008", "July-2008", "Aug-2008", "Sept-2008", _
"Oct-2008", "Nov-2008", "Dec-2008")

Range("A1:BK1") = s
Next
End Sub

 

[Dave Peterson]

Option Explicit
Sub namefixer()

Dim Sh As Worksheet
Dim SheetNames As Variant
Dim Headers As Variant

SheetNames = Array("BOOK_PrecBallMatches", _
"SHIP_PrecBallMatches", _
"Book_Lin_Bearing_Matches", _
"Ship_Lin_Bearing_Matches", _
"Book_ProfRailMatches", _
"Ship_ProfRail_Matches", _
"Book_60_CaseMatches", _
"Ship_60Case_Matches")

Headers = Array("CustType", "Duns", "Name", "Jan-2004", _
"Feb-2004", "Mar-2004", "Apr-2004", _
"May-2004", "June-2004", "July-2004", _
"Aug-2004", "Sept-2004", "Oct-2004", _
"Nov-2004", "Dec-2004", "Jan-2005", _
"Feb-2005", "Mar-2005", "Apr-2005", _
"May-2005", "June-2005", "July-2005", _
"Aug-2005", "Sept-2005", "Oct-2005", _
"Nov-2005", "Dec-2005", "Jan-2006", _
"Feb-2006", "Mar-2006", "Apr-2006", _
"May-2006", "June-2006", "July-2006", _
"Aug-2006", "Sept-2006", "Oct-2006", _
"Nov-2006", "Dec-2006", "Jan-2007", _
"Feb-2007", "Mar-2007", "Apr-2007", _
"May-2007", "June-2007", "July-2007", _
"Aug-2007", "Sept-2007", "Oct-2007", _
"Nov-2007", "Dec-2007", "Jan-2008", _
"Feb-2008", "Mar-2008", "Apr-2008", _
"May-2008", "June-2008", "July-2008", _
"Aug-2008", "Sept-2008", "Oct-2008", _
"Nov-2008", "Dec-2008")

For Each Sh In Worksheets(SheetNames)
With Sh.Range("A1").Resize(1, UBound(Headers) - LBound(Headers) + 1)
.NumberFormat = "@" 'text
.Value = Headers
End With
Next Sh

End Sub

I formatted the range as text. If a cell is formatted as General and Feb-2008
is entered, then excel will see it as a date and change the value to February 1,
2008 (the display may not show this, but the formula bar will show that date.)

 

[Bob]

Thanks Dave. However, when I run the macro I get the following error message:

Subscript out of range. When I debug it, the following line is highlighted:
For Each Sh In Worksheets(SheetNames).

Not sure what I need to change.


Thanks.

 

[Dave Peterson]

One of the worksheet names is mistyped--or doesn't belong.

The last two look suspicious to me. They're formatted differently (underscores
in different spots.)

Thanks Dave. However, when I run the macro I get the following error message:

Subscript out of range. When I debug it, the following line is highlighted:
For Each Sh In Worksheets(SheetNames).

Not sure what I need to change.

Thanks.

 

[Bob]

I tried with just the first two sheets and I am getting the same error message.

 

[Dave Peterson]

Do you have existing sheets that are named what you posted in the array?

If you think you do, then you're wrong. There's a difference between what you
included in the code and what the actual name is on the worksheet tab.

You can test the code in a different workbook.

Add two sheets and name the sheets a and b.
Change the code to use the names a and b.
Run the macro.

I tried with just the first two sheets and I am getting the same error message.