Macro uses info from sheet it was created in, NOT the current sheet

[Ian_A_Brown]

Hi folks -

frustrating problem here. I have a workbook with over 100 sheets. I want to name each sheet with a school name in the contents in A1 of of that sheet, but first I have to un-merge A1 and separate school names from other text in the cell.

I've tried cutting/pasting school name into a separate cell.

I've tried deleting all info BUT the school name in A1 (so no cut and paste, only deleting text)

Either way, when I apply the macro to a different sheet, the school name in the original sheet I recorded the macro in shows up every time I run the macro.

Appreciate all the help I can get!

[#2]

Could you please post the macro to look at? Are you looping through all the Sheets?

[#3]

thanks for responding - I figured out the above problem, but now I have an issue where I want to go through every worksheet in a workbook and rename every sheet. Every building I reference has two sheets, so when a duplicate sheet name causes an error, I set a subroutine to create a concatenation of the first sheet name. However, the routine stops after the first two renaming episodes:

Sub Set_Up_Sheet_Name()

' Set_Up_Sheet_Name Macro
'
Dim x As Integer
x = Application.Worksheets.Count
'now cycle through all the worksheets
For i = 1 To x
Worksheets(i).Activate
ActiveSheet.Range("A1:E1").Select
Selection.UnMerge
ActiveSheet.Range("A1").Select
With Selection
.UnMerge
.Range("A1").Copy Range("F1")
'copies contents of A1 to F1 for posterity, and wraps text
.Replace What:= _
"(replace a text string here not needed for sheet name)", Replacement:=""
.Replace What:= _
"(replace a text string here not needed for sheet name)", Replacement:=""
'gets rid of all text except facility name and dates
.Replace What:= _
Chr(10), Replacement:=""
.Replace What:= _
"(text string not needed for sheet name)", Replacement:=""
.Replace What:= _
" --", Replacement:=""
.WrapText = True

End With

Set target = Range("A1")
ActiveSheet.Name = Left(target, 26) & (" Data")


If Err Then
If Err.Number = 1004 Then
Err.Clear
GoTo DuplicateName
End If
End If


DuplicateName:
Set target = Range("A1")
ActiveSheet.Name = Left(target, 31)

Next i

End Sub