Hi,
I am trying to loop through a number of worksheets in the same workbook, look for the sheet names starting with sheet (eg, sheet1, sheet 2 etc...) and change those sheets with the value in cell B1 less first 4 characters. These sheets are actually "filtered pages" from a pivot table and each sheet has a value in B1. The code below only loop through the same activesheet but not actually doing anything. Help please.
Sub Loop_Rename()
Dim ws As Worksheets
Dim ct As Long, cr As String
cr = ActiveSheet.Range("B1").Value
ct = Len(ActiveSheet.Range("B1").Value) - 4
On Error Resume Next
For Each ws In ActiveWorkbook
If ws.name = "Sheet*" Then
ws.name = right(cr, ct).Value
Else
End If
Next ws
End Sub
I am trying to loop through a number of worksheets in the same workbook, look for the sheet names starting with sheet (eg, sheet1, sheet 2 etc...) and change those sheets with the value in cell B1 less first 4 characters. These sheets are actually "filtered pages" from a pivot table and each sheet has a value in B1. The code below only loop through the same activesheet but not actually doing anything. Help please.
Sub Loop_Rename()
Dim ws As Worksheets
Dim ct As Long, cr As String
cr = ActiveSheet.Range("B1").Value
ct = Len(ActiveSheet.Range("B1").Value) - 4
On Error Resume Next
For Each ws In ActiveWorkbook
If ws.name = "Sheet*" Then
ws.name = right(cr, ct).Value
Else
End If
Next ws
End Sub
