(Solved) – VBA – Runtime Error 1004 : Application Defined or Object Defined Error


Hi all i am trying to make a macro to get an array of values inside column A of Sheet ‘Codes’ and then get an array of all Sheets whose name starts with ‘Price_Plan’

FYI My Start_Click Button is in a Sheet Named ‘Macro’ and an I am trying to get data from column ‘A’ of Sheet named ‘Codes’

Below is the code

Private Sub Start_Click_Click()
    Dim wS As Worksheet, LastRow As Long
    Dim codeArray As Variant
    Dim lastCell As String
    Dim curSheet As Worksheet
    Dim ArraySheets() As String
    Dim x As Variant

    Set wS = ThisWorkbook.Worksheets("Codes")

    'Here we look in Column A
    LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
    lastCell = "A" & Str(LastRow)

    'Here we create array of our row values
    Set codeArray = ActiveSheet.range("A2:" & lastCell).Value

    'Getting Array of Price Plan Sheets
    For Each curSheet In ActiveWorkbook.Worksheets
        If curSheet.Name Like "Price_Plan*" Then
            ReDim Preserve ArraySheets(x)
            ArraySheets(x) = curSheet.Name
            x = x   1
        End If
    Next curSheet

End Sub

I am getting this Runtime Error at below step

Set codeArray = ActiveSheet.range("A2:" & lastCell).Value

Could anyone explain why this is happening and how i can fix this?

Leave a Reply

Your email address will not be published. Required fields are marked *