mardi 12 mai 2020

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

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

Aucun commentaire:

Enregistrer un commentaire