VBA将Excel列复制到不同的工作簿并关闭文件

Question

我有这个基于工作的项目，VBA会自动将选择列复制到不同工作簿中的某些工作表。

Sub Macro2() 
MsgBox "It's working 2" 


Dim incidentDataColumnC As Range, SpecificDataColumnA As Range 

Set incidentDataColumnC = Workbooks("01 - January 2017 Incident Data.csv").Worksheets(1).Columns("C") 
Set SpecificDataColumnA = Workbooks.Open("Z:\****\Real Test\01 - January 2017 Service Desk Reports.xlsx").Worksheets(3).Columns("A") 

incidentDataColumnC.Copy Destination:=SpecificDataColumnA 
SpecificDataColumnA.Parent.Close 

Dim incidentDataColumnE As Range, SpecificDataColumnB As Range 

Set incidentDataColumnE = Workbooks("01 - January 2017 Incident Data.csv").Worksheets(1).Columns("E") 
Set SpecificDataColumnB = Workbooks.Open("Z:\****\Real Test\01 - January 2017 Service Desk Reports.xlsx").Worksheets(3).Columns("B") 


incidentDataColumnE.Copy Destination:=SpecificDataColumnB 
SpecificDataColumnB.Close 

Dim incidentDataColumnH As Range, SpecificDataColumnC As Range 

Set incidentDataColumnH = Workbooks("01 - January 2017 Incident Data.csv").Worksheets(1).Columns("H") 
Set SpecificDataColumnC = Workbooks.Open("Z:\****\Real Test\01 - January 2017 Service Desk Reports.xlsx").Worksheets(3).Columns("C") 

incidentDataColumnH.Copy Destination:=SpecificDataColumnC 
SpecificDataColumnC.Close 
.... 
.... 
MsgBox "It ran" 
End Sub 

该代码的功能应该是通过复制列的方式，但它返回一个错误，说明目标文件已打开。

我想找到解决此错误的方法，并且如果可能在复制过程运行后关闭每个文件。

我尝试了一个简单的.Close命令，但弹出一个错误，说它不受支持。

Answer 1

您应该使用Workbook对象来跟踪您的工作簿。您也可以通过直接复制范围来简化代码，而不是将对象分配给每个范围。

Sub Macro2() 
    Dim wb1 As Workbook 
    Dim wb2 As Workbook 
    MsgBox "It's working 2" 

    Set wb1 = Workbooks("01 - January 2017 Incident Data.csv") 
    Set wb2 = Workbooks.Open("Z:\****\Real Test\01 - January 2017 Service Desk Reports.xlsx") 

    wb1.Worksheets(1).Columns("C").Copy wb2.Worksheets(3).Columns("A") 
    wb1.Worksheets(1).Columns("E").Copy wb2.Worksheets(3).Columns("B") 
    wb1.Worksheets(1).Columns("H").Copy wb2.Worksheets(3).Columns("C") 
    '.... 
    '.... 
    wb2.Close 
    MsgBox "It ran" 
End Sub