我刚刚开始学习VBA，并且对以下内容有些困惑。谢谢您的协助。
通过以下操作，我得到了:编译错误:在ElseIf (ActiveSheet.Name) = "BA Tracker" Then行的folderPathWithName As String之后，当前作用域中有重复项。
我的假设是，最初的If中的内容不会影响后续的ElseIf。如果不是这种情况，那么我真的不确定要从ElseIf中拿出什么来使这项工作生效。
谢谢你的帮助。
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
最佳答案
您要声明两次folderPathWithName变量-一次在If块中，然后在'ElseIf`块中。
只需从ElseIf块内删除Dim folderPathWithName As String行，并将Dim folderPathWithName As String块内的If行与所有其他变量声明一起移动即可。
我建议您始终在过程开始时声明所有变量，而不是在您认为需要它们时声明它们。这样可以阻止这种情况的发生，并使代码保持整洁。
问候，
关于excel - VBA If/ElseIf-编译错误: Duplication in Current Scope，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/61748824/