Solution to change cell range to array
In the below solution, I have made a Sub to define array (defineArray), a Sub to delete worksheet(delWS), a Sub to move worksheet(moveWS)
First, I define an array at Module level, so that all Sub within the Module can access the array.
In Sub defineArray(), I put all the items from Range A4 to the last row of column A into wsArray()
In Sub delWS(), I put “On Error Resume Next” to prevent error message when Macro tries to delete a worksheet that does not exist
“Application.DisplayAlerts = False” prevents the confirmation message box to delete a worksheet
Dim wsArray() As Variant Public Sub defineArray() For i = 4 To Range("A" & Rows.Count).End(xlUp).Row ReDim Preserve wsArray(i) wsArray(i) = Range("A" & i).Value Next i End Sub Public Sub delWS() On Error Resume Next For i = 4 To UBound(wsArray()) Application.DisplayAlerts = False Sheets(wsArray(i)).Delete Application.DisplayAlerts = True Next i On Error GoTo 0 End Sub Public Sub moveWS() On Error Resume Next For i = 4 To UBound(wsArray()) Workbooks("Book1").Activate Workbooks("Book1").Sheets(wsArray(i)).Move after:=Workbooks("Book.xlsx").Sheets(1) Next i On Error GoTo 0 End Sub