Object Variable or With Block variable not set

Question

OBJECTIVE

Business units submit monthly financial information to Corporate each month. Develop a VBA macro that will pull in financial data from multiple business units.

APPROACH

Create a MASTER workbook that consolidates each business unit's information. Make sure that each business unit is represented in the MASTER workbook ("Target_workbook") with their own tab (e.g "1120", "1130", "1210"; "businessUnit")
Create an array from each of the business unit tabs ("arr")
Use array information to find corresponding Monthly financial report ("Source_Workbook", "Source_Path")
Copy and paste financial information into the MASTER workbook ("Target_workbook") and corresponding business unit's tab (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

ISSUES

Error @ Line: Target_Workbook.Sheets(arr(i)).Range("A5").PasteSpecial Paste:=xlPasteValues, "Object Variable or With Block variable not set". Why is this the case? Is it because the arr(i) is a variant/string?
Any other suggestions for code improvement?

None of the hundreds of existing posts with the same error message helped you? I know I've seen this exact issue (with the same solution the answer here provides) many times. Please do some basic research before posting a new question here, which would include reading a large number of the search results you'd get searching on the exact error message you included. There are 10 already mentioned to the right in the Related list ===>>>, which the site suggested as potential duplicates when you were writing your post, without even needing to search. — Ken White

Verity Verity · Accepted Answer · 2016-09-27T21:58:32

You have Set Target_Workbooks , remove the s.

Object Variable or With Block variable not set

1 Answers