Solved: Write a vba variable out to a sheet

[JimS]

The code below renames any sheet that begins with the name "Sheet".

Can this be modified to create a list on another sheet of every new sheet name that this code changes?

In example on a sheet labled "Names" starting in cell A2 would be the first new name of the first sheet that this code changed, and then in cell A3 would be the next new name that gets changed, and so on down column A for every sheet that had a name change by this code.

It somehow would have to write the value of xxx out everytime it renamed a sheet.

Thanks...

Jim


[vba]Sub ShtRename()

Application.ScreenUpdating = False

For Each sht In ActiveWorkbook.Sheets
xxx = Empty

If Left(sht.Name, 5) = "Sheet" Then
On Error Resume Next
xxx = Split(Split(sht.Range("B5").Value, "<A href="file://\\")(1">\\")(1), ".")(0)
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx

End If
Next sht

Application.ScreenUpdating = True

End Sub
[/vba]

[Benzadeus]

[VBA]Sub ShtRename()

Dim sht As Worksheet
Dim xxx As Variant
Dim shChanges As Worksheet
Dim n As Long

Application.ScreenUpdating = False

Set shChanges = Sheets.Add
n = 2 'offset that will write sheets that were renamed on the new created sheet

For Each sht In ActiveWorkbook.Sheets
If sht.Index > 1 Then
xxx = Empty

If Left(sht.Name, 5) = "Sheet" Then
shChanges.Cells(n, "A") = sht.Name
n = n + 1

On Error Resume Next
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx

End If
End If
Next sht

Application.ScreenUpdating = True

End Sub
[/VBA]

[JimS]

I don't need it to create a new sheet to write out to - the sheet that I want it to write the names to is called "Names".

Thanks for your help.

[Benzadeus]

[VBA]Sub ShtRename()

Dim sht As Worksheet
Dim xxx As Variant
Dim shChanges As Worksheet
Dim n As Long

Const strChanges As String = "Sheets Changed" '<-- Change to suit

Application.ScreenUpdating = False

Set shChanges = Sheets(strChanges)
n = 2 'offset that will write sheets that were renamed on the report sheet

For Each sht In ActiveWorkbook.Sheets
If sht.Name <> shChanges.Name Then
xxx = Empty

If Left(sht.Name, 5) = "Sheet" Then
shChanges.Cells(n, "A") = sht.Name
n = n + 1

On Error Resume Next
'I deleted the "split" line because it was bugging when posting on forum.
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx

End If
End If
Next sht

Application.ScreenUpdating = True

End Sub[/VBA]

[JimS]

Can I get it to write the "new" name out. Right now it's writing out the original sheet name before it is changed.

[JimS]

Got it to work - Thansk...

Jim

[VBA]
Sub ShtRename()

Dim sht As Worksheet
Dim xxx As Variant
Dim shChanges As Worksheet
Dim n As Long

Const strChanges As String = "Changed Names" '<-- Change to suit

Application.ScreenUpdating = False

Set shChanges = Sheets(strChanges)
n = 2 'offset that will write sheets that were renamed on the report sheet

For Each sht In Worksheets
If sht.Name <> shChanges.Name Then
xxx = Empty

If Left(sht.Name, 5) = "Sheet" Then
'shChanges.Cells(n, "A") = sht.Name
'n = n + 1

On Error Resume Next
xxx = Split(Split(sht.Range("B5").Value, "<A href="file://\\")(1">\\")(1), ".")(0)
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx

'sht.Name = xxx
shChanges.Cells(n, "A") = sht.Name
n = n + 1

End If
End If
Next sht
[/VBA]