VBA If / ElseIf - Compile Error: Duplication in Current Scope

Question

I'm just getting started on learning VBA and am a bit stumped on the following. I'd be grateful for your assistance.

With the following I'm getting: Compile Error: Duplication in Current Scope after ElseIf (ActiveSheet.Name) = "BA Tracker" Then at line folderPathWithName As String.

My assumption had been that what's in the initial If wouldn't impact the subsequent ElseIf. If that isn't the case then I'm really not sure what to take out of the ElseIf to make this work.

Thanks for your help.

Sub CopyFile()

    Dim oFSO As Object
    Dim SourceFile As String
    Dim DestinationFolder As String
    Dim startPath As String
    Dim myName As String
    Dim FileYear As String
    Dim FileMonth As String
    Dim AgentName As String
    Dim Agreement As String
    Dim CallDate As String
    Dim wb As Workbook
    Dim ws1112 As Worksheet
    Dim ws2221 As Worksheet
    Dim s As String
    Dim r As String
    Dim cst As String
    Dim cd As String
    Dim ass As String
    Dim ty As String
    Dim an As String
    Dim ss As String
    Dim si As String
    Dim sour As String


    FileYear = Range("A2")
    FileMonth = Range("A3")
    AgentName = Range("D1")
    Agreement = Range("D2")
    CallDate = Range("D3")

If (ActiveSheet.Name) = "Sitel Audit" Then

    startPath = "C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\"
    myName = ActiveSheet.Range("D1").Text  ' Change as required to cell holding the folder title

' check if folder exists, if yes, end, if not, create
   Dim folderPathWithName As String
    folderPathWithName = startPath & Application.PathSeparator & myName

    If Dir(folderPathWithName, vbDirectory) = vbNullString Then
        MkDir folderPathWithName

    End If

Set oFSO = CreateObject("Scripting.FileSystemObject")

SourceFile = "C:\Users\matthew.varnham\Desktop\QA Improvements\Customer service Inbound scorecard v9.xlsm"
DestinationFolder = "C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\" & AgentName & "\"

oFSO.CopyFile Source:=SourceFile, Destination:=DestinationFolder & "\" & AgentName & " - " & Agreement & ".xlsm"

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 12), Address:=("C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm"), TextToDisplay:="OPEN"

Set ws1112 = Sheets("Sitel Audit")
s = ws1112.Range("D1").Value 'Agent Name
r = ws1112.Range("D3").Value 'Call Date
cst = ws1112.Range("D4").Value 'Call Start Time
cd = ws1112.Range("D5").Value 'Call Duration
ass = ws1112.Range("D6").Value 'Assessor Initials
ty = ws1112.Range("D7").Value 'Call Type
an = ws1112.Range("D2").Value 'Agreement Number
ss = ws1112.Range("D8").Value 'Sitel Score
si = ws1112.Range("E1").Value & FileYear & "\" & FileMonth & "\" & AgentName & "\" 'Sitel QA Folder
sour = ws1112.Range("A4").Value 'Sitel as Source


Set wb = Workbooks.Open("C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm")
Set ws2221 = wb.Sheets("Observation Sheet")

ws2221.Range("B5:C5").Value = s 'Agent Name
ws2221.Range("E5").Value = r 'Call Date
ws2221.Range("F5").Value = cst 'Call Start Time
ws2221.Range("G5").Value = cd 'Call Duration
ws2221.Range("B8:C8").Value = ass 'Assessor Initials
ws2221.Range("B11:C11").Value = ty 'Call Type
ws2221.Range("E8:G8").Value = an 'Agreement Number
ws2221.Range("D4").Value = ss 'Sitel Score
ws2221.Range("G51").Value = si 'Sitel QA Folder
ws2221.Range("C3").Value = sour 'Sitel as Source

ElseIf (ActiveSheet.Name) = "BA Tracker" Then

    startPath = "C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\"
    myName = ActiveSheet.Range("D1").Text  ' Change as required to cell holding the folder title

' check if folder exists, if yes, end, if not, create
   Dim folderPathWithName As String
    folderPathWithName = startPath & Application.PathSeparator & myName

    If Dir(folderPathWithName, vbDirectory) = vbNullString Then
        MkDir folderPathWithName

    End If

Set oFSO = CreateObject("Scripting.FileSystemObject")

SourceFile = "C:\Users\matthew.varnham\Desktop\QA Improvements\Customer service Inbound scorecard v9.xlsm"
DestinationFolder = "C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\" & AgentName & "\"

oFSO.CopyFile Source:=SourceFile, Destination:=DestinationFolder & "\" & AgentName & " - " & Agreement & ".xlsm"

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 13), Address:=("C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm"), TextToDisplay:="OPEN"

Set ws1112 = Sheets("BA Tracker")
s = ws1112.Range("D1").Value 'Agent Name
r = ws1112.Range("D3").Value 'Call Date
cst = ws1112.Range("D4").Value 'Call Start Time
cd = ws1112.Range("D5").Value 'Call Duration
ass = ws1112.Range("D6").Value 'Assessor Initials
ty = ws1112.Range("D7").Value 'Call Type
an = ws1112.Range("D2").Value 'Agreement Number
ss = ws1112.Range("D8").Value 'Sitel Score
si = ws1112.Range("E1").Value & FileYear & "\" & FileMonth & "\" & AgentName & "\" 'Sitel QA Folder
sour = ws1112.Range("A4").Value 'Sitel as Source


Set wb = Workbooks.Open("C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm")
Set ws2221 = wb.Sheets("Observation Sheet")

ws2221.Range("B5:C5").Value = s 'Agent Name
ws2221.Range("E5").Value = r 'Call Date
ws2221.Range("F5").Value = cst 'Call Start Time
ws2221.Range("G5").Value = cd 'Call Duration
ws2221.Range("B8:C8").Value = ass 'Assessor Initials
ws2221.Range("B11:C11").Value = ty 'Call Type
ws2221.Range("E8:G8").Value = an 'Agreement Number
ws2221.Range("D4").Value = ss 'Sitel Score
ws2221.Range("G51").Value = si 'Sitel QA Folder
ws2221.Range("C3").Value = sour 'Sitel as Source


End If

Workbooks("SITEL - Inbound Tracker.XLSM").Close SaveChanges:=True

End Sub

It would help if you got in the habit of indenting your code. Not only does it look better, but it makes it more readable and easier to spot some mistakes. In this case just remove the second `Dim folderPathWithName As String` as you can only declare a variable once. — SJR, May 12 '20 at 10:10
And probably better to declare it ouside an `If` in case your logic skips that section. (Though `Dim` lines may be acknowledged either way, I'm not sure.) — SJR, May 12 '20 at 10:11
Also not advisable to refer to activesheet or activecell. Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, May 12 '20 at 10:36

score 0 · Accepted Answer · answered May 12 '20 at 10:13

0

You are declaring the variable folderPathWithName twice - once inside in the If block, and then within the 'ElseIf` block.

Just delete the line Dim folderPathWithName As String from within the ElseIf block, and move the line Dim folderPathWithName As String from within the If block to be with all of the other variable declarations.

I would suggest that you always declare all of your variables at the start of the procedure, rather than when you think that you will need them. This stops this from happening, and also keeps your code tidy.

Regards,

answered May 12 '20 at 10:13

Applecore

3,934
2
9
13

"declare all of your variables at the start of the procedure" - that's what I do but I know some people disagree and think it better to declare variables at the point at which they are used. – SJR May 12 '20 at 10:18
And that's why we won't get this error!! And also, it is easier when doing code cleanup (setting objects to nothing and closing/quitting them) to see everything used in the procedure all in one place. – Applecore May 12 '20 at 10:28

VBA If / ElseIf - Compile Error: Duplication in Current Scope

1 Answers1