Duplicate declaration error help

[Girth]

Can anyone help with the following problem? I'm trying to rename a sheet, perform some actions, rename another sheet & then perform some other actions, all in the same module.

I keep getting a 'duplicate declaration' error relating to the xStr & retry: entries in the code, obviously because they are duplicated & I don't know how to rectify this.

Any ideas?

' Sub goto_pmnt_file Macro

    'copy & unprotect master payment schedule
    Windows("XXX Subcontract Payment Records.xls").Activate
    
    Application.ScreenUpdating = False
    
    Sheets("SPA Master").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("SPA Master").Copy After:=Worksheets(Worksheets.Count)
    Sheets("SPA Master (2)").Select
    ActiveSheet.Unprotect Password:="xxxxx"
    
    'rename master payment schedule
     Dim xStr As String
retry:
    Err.Clear
     xStr = InputBox("Enter name for new SPA Schedule" _
       & vbCrLf & "use HEAD CODE & SHORT CODE & SPA" _
       & vbCrLf & "e.g. '3075 WOOD02 SPA'", ActiveSheet.Name)
    If xStr = "" Then
    Sheets(Worksheets.Count).Select
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Windows("SUBCON ORDER1.xls").Activate
    End If
    Exit Sub
        
    On Error Resume Next
    ActiveSheet.Name = xStr
    If Err.Number <> 0 Then
        MsgBox ("Try Again!" _
        & vbCrLf & "Invalid name or schedule already exists" _
        & vbCrLf & "Please name the new SPA Schedule")
        Err.Clear
        GoTo retry
    End If
    On Error GoTo 0
    
    
    
    
    'write site number & contractor to new schedule
    Windows("SUBCON ORDER1.xls").Activate
    Range("E6").Select
    Selection.Copy
    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets(Worksheets.Count).Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SUBCON ORDER1.xls").Activate
    Range("B11").Select
    Selection.Copy
    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets(Worksheets.Count).Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'copy master payment sheet
    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets("PMNT Master").Select
    Sheets("PMNT Master").Copy Before:=Worksheets(Worksheets.Count)
    
    
    
    
    
    'rename master payment sheet
    Dim xStr As String
retry:
    Err.Clear
     xStr = InputBox("Enter name for new Payment Sheet" _
       & vbCrLf & "use HEAD CODE & SHORT CODE & PMNT" _
       & vbCrLf & "e.g. '3075 WOOD02 PMNT'", ActiveSheet.Name)
    If xStr = "" Then
    Sheets(Worksheets.Count).Select
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Windows("SUBCON ORDER1.xls").Activate
    End If
    Exit Sub
        
    On Error Resume Next
    ActiveSheet.Name = xStr
    If Err.Number <> 0 Then
        MsgBox ("Try Again!" _
        & vbCrLf & "Invalid name or sheet already exists" _
        & vbCrLf & "Please name the new Payment Sheet")
        Err.Clear
        GoTo retry
    End If
    On Error GoTo 0
    
    
     
    
    
    'write references to new payment sheet
    Windows("SUBCON ORDER1.xls").Activate
    Range("c59").Select
    Selection.Copy
    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets(Worksheets.Count - 1).Select
    Range("b3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("SUBCON ORDER1.xls").Activate
    Range("b11").Select
    Selection.Copy
    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets(Worksheets.Count - 1).Select
    Range("b9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("SUBCON ORDER1.xls").Activate
    Range("e11").Select
    Selection.Copy
    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets(Worksheets.Count - 1).Select
    Range("b10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("XXX Subcontract Payment Records.xls").Activate
    Sheets(Worksheets.Count).Select

    Application.ScreenUpdating = True

End Sub

 

[Jonmo1]

The error is pretty self explainatory, you've dimmed a variable twice, and have the same Goto Line twice.

Dim xStr As String
retry:

That is in your macro Twice.

You can only Dim a variable once.
So just remove the 2nd Dim xStr line..It only needs to be in there once.

And you can't have 2 goto lines with the same name.
Otherwise, when you write

Goto retry

Since there are 2 of those lines, How does it know which one to go to?

Change the 2nd one to retry2 or something...

 

[Girth]

Thanks jonmo1, have changed the 2nd references to yStr & retry2: & now there is no duplicate declaration error but now the code copies the original sheet & launches the 1st msgbox & when the new sheet name is entered & ok selected nothing happens, it simply copies the original sheet & doesn't go on to perform the other actions & copy the 2nd sheet?

 

[Jonmo1]

I would rethink your method of renaming the sheet.
using goto's is referred to as Speghetti code, and can get confusing to read and troubleshoot.

Try this basic example for better method..

Sub test()
Dim NewName As String
Do
    NewName = InputBox("enter new name")
    On Error Resume Next
    ActiveSheet.Name = NewName
    If Err.Number <> 0 Then MsgBox "Try Again"
Loop Until Err.Number = 0
On Error GoTo 0
End Sub