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 ThisWorkbook.Sheets("Codes").Activate 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?