Getting this message: "This won't work because it would move cells in a table on your worksheet."

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