VBA for Array of sheet values

Create a dynamic named range SheetNames, with nothing else in column G of Lists - with the refers to being

=OFFSET(Lists!$G$2,0,0,COUNTA(Lists!$G:$G),1)

or this, is your list has a header -

=OFFSET(Lists!$G$2,0,0,COUNTA(Lists!$G:$G)-1,1)

and use code like this:

Sub TestMacro()
 
    Dim N() As String
    Dim i As Integer
    
    ReDim N(1 To Range("SheetNames").Cells.Count)
    For i = 1 To Range("SheetNames").Cells.Count
        N(i) = Range("SheetNames").Cells(i).Value
    Next i
    Worksheets(N).Copy
    
End Sub