Macro getting stuck

[jlingo]

I'm trying to write a macro for excel that will find the data in a selected cell (H5 in my project). It needs to replace any special character such as "/" with a "-", then truncate the data down to 31 characters so that the data can be used to rename the appropriate worksheet. If there are multiple worksheets in the workbook, it needs to continue to the next worksheet and perform the same functions as before...until its done.

Here is the code I'm trying to use. It runs until it reaches the first instance of a special character or more than 31 characters and fails to apply the changes requested in the macro. Any suggestions?

Sub Rename()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Document map" Then
Range("H5").Replace What:="/", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("H5") = Left(Range("H5"), 31)
ws.Select
ws.Name = Range("H5").Value
End If
Next ws
End Sub

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Sub[/color] Rename()

    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [color=darkblue]With[/color] ws
            [color=darkblue]If[/color] .Name <> "Document map" [color=darkblue]Then[/color]
                .Range("H5").Replace What:="/", Replacement:="-", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=[color=darkblue]False[/color], _
                    ReplaceFormat:=False
                .Range("H5").Value = Left(.Range("H5").Value, 31)
                .Name = .Range("H5").Value
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] ws

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Note, however, as it stands, the code will generate an error if H5 is empty. To avoid the error, try the following instead...

[font=Verdana][color=darkblue]Sub[/color] Rename()

    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [color=darkblue]With[/color] ws
            [color=darkblue]If[/color] .Range("H5").Value <> "" [color=darkblue]Then[/color]
                [color=darkblue]If[/color] .Name <> "Document map" [color=darkblue]Then[/color]
                    .Range("H5").Replace What:="/", Replacement:="-", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=[color=darkblue]False[/color], _
                        ReplaceFormat:=False
                    .Range("H5").Value = Left(.Range("H5").Value, 31)
                    .Name = .Range("H5").Value
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] ws

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[jlingo]

Works like a charm. Thanks so much for your help.

 

[jlingo]

This code works perfectly, but of course as we all know, someone always has something else in mind when they review the final product.

I need to run the same macro but somehow keep the data in the H5 cell the same. Isn't there a way to add a separate Dim to store the data while the changes are made, then only use that changed data to rename the worksheet while leaving the H5 data un-touched?

Forgive me if my verbiage is wrong...I'm just getting my hands dirty with this stuff.

Thanks

 

[Domenic]

Try...

[FONT=Verdana][COLOR=#000000][FONT=Verdana][COLOR=darkblue]Sub[/COLOR] Rename()[/FONT][/COLOR][/FONT]
 
[FONT=Verdana][FONT=Verdana][COLOR=#000000]  [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet[/COLOR][/FONT]
 
[COLOR=#000000][FONT=Verdana]  [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] ActiveWorkbook.Worksheets[/FONT]
[FONT=Verdana]      [COLOR=darkblue]With[/COLOR] ws[/FONT]
[FONT=Verdana]           [COLOR=darkblue]If[/COLOR] .Name <> "Document map" [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]             [COLOR=darkblue]If[/COLOR] .Range("H5").Value <> "" [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]                  .Name = Left(Replace(.Range("H5").Value, "/", "-", 1, -1, vbTextCompare), 31)[/FONT]
[FONT=Verdana]              [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
[FONT=Verdana]          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
[FONT=Verdana]      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]
[FONT=Verdana]  [COLOR=darkblue]Next[/COLOR] ws[/FONT]
[/COLOR] 
[FONT=Verdana][COLOR=#000000][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/COLOR][/FONT]
[/FONT]

 

[jlingo]

Brilliant! Thanks again!

 

[Domenic]

You're very welcome!