Help needed to troubleshoot VBA code, for creation of a summary sheet.

[Novelec]

Hi forum members,

I am obviously lacking in the VBA knowledge department, and am trying to create a summary sheet consisting of data that is collected from five other worksheets in the same workbook. On the sheets the data is being collected from I am checking various named ranges for the value "a", and would like to copy the cell immediately to the right of the cells that match this criteria. All cells identified are then to be pasted to a specified area on the summary worksheet.

I have managed to complete the first stage of this task, and can paste matching values to the summary sheet. However I am only successful when I refer to a single named range. I receive all sorts of errors when I add additional named ranges to the process using the "Union" function.

I am also having problems when the macro counts the number of rows to insert on the summary sheet - The summary sheet needs to be dynamic, so new rows need to be inserted for the data to be pasted to. Data will eventually come from five separate sheets, each being pasted to a unique location on the summary sheet. I have started with only one sheet (named "Basement") for troubleshooting purposes.

As always, any assistance received is greatly appreciated.

The code is shown below. The error received when trying to count rows for inserting is a "Run-time error '1004': Application-defined or object-defined error", which highlights the following line of code:

Code:

[COLOR=#ff0000]Number_of_Basement_Rows = Sheets("Basement").Range("Basement_No_Checks").Rows.Count[/COLOR]

Full Code:

Sub Summary_Compiler_Edit()
     
    Dim Basement_No_Checks As Range
    Dim Basement_range As Range
    Dim Number_of_Basement_Rows As Integer
    Dim Cell As Range
    
'The following union operation normally sits on one line, but is giving errors

    [COLOR=#ff0000]Set Basement_range = Sheets("Basement").Range(Union(Range("Generator_Room_No_Checks"), Range("Generator_Room_No_Checks_Remote"), Range("Generator_Control_Room_No_Checks"), Range("UPS_2_4_Room_No_Checks"), Range("Generator_Main_Switchroom_No_Checks"), Range("Lift_Motor_Room_No_Checks"), Range("Main_Corridor_Basement_No_Checks"), Range("UPS_1_3_5_Room_No_Checks"), Range("Battery_Room_No_Checks"), Range("Battery_Room_1A_No_Checks"), Range("Battery_Room_2_No_Checks"), Range("Battery_Room_3_No_Checks"), Range("Battery_Room_4_No_Checks"), Range("Battery_Room_5_No_Checks"), Range("Battery_Room_6_No_Checks"), Range("Battery_Room_7_No_Checks"), Range("UPS_6_7_Room_No_Checks"), Range("Fuel_Pump_Room_No_Checks"), Range("Undercroft_No_Checks")))[/COLOR]
     
    For Each Cell In Basement_range
        
        If Cell.Text = "a" Then
            If Basement_No_Checks Is Nothing Then
                Set Basement_No_Checks = Cell.Offset(0, 1)
            Else
                Set Basement_No_Checks = Union(Basement_No_Checks, Cell.Offset(0, 1))
            End If
        End If
    Next
    
    If Basement_No_Checks Is Nothing Then
        MsgBox "There are no failed checks for the Basement area"
    Else
[COLOR=#ff0000]Number_of_Basement_Rows = Sheets("Basement").Range("Basement_No_Checks").Rows.Count[/COLOR]
        'Rows("Number_of_Basement_Rows").Insert Shift:=xlDown
        Basement_No_Checks.Copy Destination:=Sheets("Summary").Range("B9").End(xlUp).Offset(1)
    End If
    
End Sub

​

 

[Novelec]

Still struggling with this... I've fixed my issue with the union function, but still struggling to get a count for rows to be inserted. Anyone have a suggestion...?