Column Headings Repeating
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.
Bob wrote:
I tried with just the first two sheets and I am getting the same error message.
--
Bob
"Dave Peterson" wrote:
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.)
Bob wrote:
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
"Dave Peterson" wrote:
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 wrote:
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
--
Bob
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
|