truchiller
New Member
- Joined
- Jan 11, 2010
- Messages
- 4
I recently built two macros through the help of friends and google to take and build a Table of Contents - based off of an unlimited number of worksheets within a workbook. A second Macro was built to replace the information from the TOC which inputs in Column A with new information put into Column B - however the information I am inputting into Column B to rename the worksheet tabs are dates - currently formatted as dd mmm yy. When I run the macro it does not return the dates - it will only return a value if I change the format of the column to general - which places to Unicode of the date into the worksheet Tab... I am either missing a formatting string in my macro or question if I have to build another macro to translate the Unicode string into a "dd mmm yy" format. Any assistance would be great. I am creating a new schedule to where the end user only needs to change one date from year to year and all values will update after both macro's are run.
Here are the two macro's built -
Sub CreateTOC()
Dim NumSheets As Integer
'creates Table Of Contents
NumSheets = Sheets.Count
For j = 1 To NumSheets
Worksheets("TOC").Cells(j, 1) = Sheets(j).Name
Next j
End Sub
----------------------------------------------------------------------
Sub ChangeSheetNames()
Dim ws As Worksheet
Dim r As Range
Dim s As Range
Set r = Worksheets("TOC").Range("A1", Range("A65536").End(xlUp))
For Each s In r.Cells
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "TOC" Then
If ws.Name = s Then
ws.Name = s.Offset(0, 1)
On Error Resume Next 'Will continue if an error results
End If
End If
Next ws
Next s
End Sub
Column A (TOC) = the data extracted from the TOC Macro
Column B (TOC) = the new worksheet tab name assignments currently equal to values set in Column C
Column C (TOC)= dates and formulas - currently C2 equals 3 Jan 10 and C3 equals (C2+7) and so on throughout the column.
Col A Col B Col C
<TABLE cellSpacing=0 cellPadding=1 border=1><TBODY><TR><TD>TOC</TD><TD>Macro Ref</TD><TD colSpan=2>Change Formula</TD></TR><TR><TD>40181</TD><TD>03 Jan 10</TD><TD>03 Jan 10</TD><TD>Change this entry only</TD></TR><TR><TD>40188</TD><TD>10 Jan 10</TD><TD>10 Jan 10</TD></TR><TR><TD>40195</TD><TD>17 Jan 10</TD><TD>17 Jan 10</TD></TR><TR><TD>40202</TD><TD>24 Jan 10</TD><TD>24 Jan 10</TD></TR><TR><TD>40209</TD><TD>31 Jan 10</TD><TD>31 Jan 10</TD></TR><TR><TD>40216</TD><TD>07 Feb 10</TD><TD>07 Feb 10</TD></TR><TR><TD>40223</TD><TD>14 Feb 10</TD><TD>14 Feb 10</TD></TR><TR><TD>40230</TD><TD>21 Feb 10</TD><TD>21 Feb 10</TD></TR><TR><TD>40237</TD><TD>28 Feb 10</TD><TD>28 Feb 10</TD></TR></TBODY></TABLE>
thanks for anyones help in resolving my missing link...
- J
Here are the two macro's built -
Sub CreateTOC()
Dim NumSheets As Integer
'creates Table Of Contents
NumSheets = Sheets.Count
For j = 1 To NumSheets
Worksheets("TOC").Cells(j, 1) = Sheets(j).Name
Next j
End Sub
----------------------------------------------------------------------
Sub ChangeSheetNames()
Dim ws As Worksheet
Dim r As Range
Dim s As Range
Set r = Worksheets("TOC").Range("A1", Range("A65536").End(xlUp))
For Each s In r.Cells
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "TOC" Then
If ws.Name = s Then
ws.Name = s.Offset(0, 1)
On Error Resume Next 'Will continue if an error results
End If
End If
Next ws
Next s
End Sub
Column A (TOC) = the data extracted from the TOC Macro
Column B (TOC) = the new worksheet tab name assignments currently equal to values set in Column C
Column C (TOC)= dates and formulas - currently C2 equals 3 Jan 10 and C3 equals (C2+7) and so on throughout the column.
Col A Col B Col C
<TABLE cellSpacing=0 cellPadding=1 border=1><TBODY><TR><TD>TOC</TD><TD>Macro Ref</TD><TD colSpan=2>Change Formula</TD></TR><TR><TD>40181</TD><TD>03 Jan 10</TD><TD>03 Jan 10</TD><TD>Change this entry only</TD></TR><TR><TD>40188</TD><TD>10 Jan 10</TD><TD>10 Jan 10</TD></TR><TR><TD>40195</TD><TD>17 Jan 10</TD><TD>17 Jan 10</TD></TR><TR><TD>40202</TD><TD>24 Jan 10</TD><TD>24 Jan 10</TD></TR><TR><TD>40209</TD><TD>31 Jan 10</TD><TD>31 Jan 10</TD></TR><TR><TD>40216</TD><TD>07 Feb 10</TD><TD>07 Feb 10</TD></TR><TR><TD>40223</TD><TD>14 Feb 10</TD><TD>14 Feb 10</TD></TR><TR><TD>40230</TD><TD>21 Feb 10</TD><TD>21 Feb 10</TD></TR><TR><TD>40237</TD><TD>28 Feb 10</TD><TD>28 Feb 10</TD></TR></TBODY></TABLE>
thanks for anyones help in resolving my missing link...
- J