loop truncating and renaming sheet vba excel

[tuytuy]

Hi,
i wrote this code to truncate a cell in a sheet, then rename it with the new value of this cell and delete the whole row afterwards. I need this to loop through all the sheets with the value of B1.
here is what i have so fare but it doesn't seem to work.

Dim workS As Worksheet
    For Each workS In ActiveWorkbook.Worksheets
' Truncating "section" cell


ActiveCell = Left(Range("B1"), 9)


' Replacing blanks by underscore for renaming purposes


ActiveCell = Replace(ActiveCell, " ", "_")


'Replacing "SheetX" with the name of the section


Dim ws1 As Worksheet
Set ws1 = ActiveSheet
ws1.Name = ws1.Range("A1")


'deleting row 1


Selection.EntireRow.Delete


    Next workS

 

[VoG]

Do you mean something like

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Name = Replace(Left(.Name, 9), " ", "_")
    End With
Next ws
End Sub

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim workS As Worksheet
    For Each workS In ActiveWorkbook.Worksheets
        With workS.Range("B1")
'           Truncating "section" cell
'           Replacing blanks by underscore for renaming purposes
            .Value = Replace(Left(.Value, 9), " ", "_")
            .Parent.Name = .Value
            .EntireRow.Delete
        End With
    Next workS
End Sub

 

[tuytuy]

Try:

Code:

Sub Test() Dim workS As Worksheet For Each workS In ActiveWorkbook.Worksheets With workS.Range("B1")' Truncating "section" cell' Replacing blanks by underscore for renaming purposes .Value = Replace(Left(.Value, 9), " ", "_") .Parent.Name = .Value .EntireRow.Delete End With Next workS </pre>

this would renam each sheet with the name of the first one.
i would want want each sheet to rename it self with their own B1 value

 

[Andrew Poulsom]

Did you try it?

 

[tuytuy]

yes, didn't work

 

[Andrew Poulsom]

It worked for me.