Duplicate declaration error help

Girth

New Member
Joined
Nov 9, 2005
Messages
8
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?

Rich (BB code):
' 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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...
 
Last edited:

Girth

New Member
Joined
Nov 9, 2005
Messages
8
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,709
Messages
5,597,697
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top