对象变量或带块变量未设置

Question

目的

业务部门每月提交每月财务信息公司。开发一个将从多个业务部门获取财务数据的VBA宏。

APPROACH

1. 创建主簿，它整合各业务部门的信息。确保每个业务单元在主簿（“Target_workbook‘）代表用自己的标签（如‘1120’，‘1130’，‘1210’，’businessUnit”）
2. 从每个业务创建一个数组单元片（“arr‘）
3. 使用数组的信息来找到相应的月度财务报告（’Source_Workbook”，“Source_Path‘）
4. 复制和财务信息粘贴到主簿（’Target_workbook”）和相应的业务单位的标签（businessUnit）

CODE

Sub getBusinessUnits() 


Dim ws As Worksheet 
Dim Target_Workbook As Workbook 
Dim Source_Workbook As Workbook 
Dim element As Variant 
Dim col As New Collection 
Dim Source_Path As String 
Dim businessUnit As String 
Dim businessName As String 


'Set up collection to identify Business Unit Tabs and convert into array 
For Each ws In ThisWorkbook.Worksheets 
    If IsNumeric(ws.Name) Then 
     col.Add ws.Name 
     Dim arr As Variant 
    End If 
Next 
arr = toArray(col) 'Collection converted into Array 



'Loop through worksheets in array, open relative workbook, and pull in relevant data 
For i = LBound(arr, 1) To UBound(arr, 1) 

    'assign business unit information to variables. 
    'Define workbook where we will paste copied information (target_workbook) 
    businessUnit = ThisWorkbook.Sheets(arr(i)).Activate 
    Set Target_Workbooks = ThisWorkbook.Sheets(arr(i)) 
    businessName = ActiveSheet.Cells(2, 2) 

    'Open up the corresponding business unit's financial report, copy data 
    Source_Path = ThisWorkbook.Path & "\Business Unit Monthly Reporting Template_" & businessName & ".xlsx" 
    Set Source_Workbook = Workbooks.Open(Source_Path) 
    Source_Workbook.Sheets("Auth Expense Data Entry").Range("A1:H150").Copy 

    'Paste copied information from Source_Workbook into Target_workbook 
    Target_Workbook.Sheets(arr(i)).Range("A5").PasteSpecial Paste:=xlPasteValues '!!!ERROR: "Object Variable or With Block variable not set" !!!! 


    'Clear cache, close source_workbook 
    Application.CutCopyMode = False 
    Source_Workbook.Close (False) 
    End 
Next 

End Sub 

---

'Function to convert collection into array 
    Function toArray(col As Collection) 
     Dim arr() As Variant 
     ReDim arr(1 To col.Count) As Variant 
     For i = 1 To col.Count 
      arr(i) = col(i) 
     Next 
     toArray = arr 
    End Function 

问题

1. 错误@行：Target_Workbook.Sheets(arr(i)).Range("A5").PasteSpecial Paste:=xlPasteValues， “对象变量或With块变量未设置”。为什么会这样？ 是否因为arr（i）是变体/字符串？
2. 其他代码改进建议？

Answer 1

您已设置Target_Workbook 小号 ，取下S。