Copy sheets (that have tables) whose names are in range VBA

[Mephistopheles86]

Hi,

I'd like to ask for your help with a vba i was writing.
Please bear in mind that i'm very ignorant on the matter and have been recently dipping my toes in vba.

I'm trying to copy all sheets listed on a range from an auxiliary sheet i created. These sheets contain tables and so i had to create a temporary new window to copy them.
So, my problem is highlighted in red below. Basically i need the line to be something like: .Sheets(array("sheet1","sheet2"))

I don't know if i'm explaining my problem clearly so please do ask what might be unclear for you.

Thanks in advance

Sub Button3_Click()

Dim TheActiveWindow As Window
Dim TempWindow As Window

Dim NewArray As Variant
  
    With ActiveWorkbook
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
       
            Dim rngg As Range
            Dim rngg2 As Range
            Dim NNewArray As Variant
           
Worksheets("aux").Activate

            Set rngg = Range("A2:G2")
            Set rngg2 = Range("A3:G3")
            rngg2.Clear
           
            y = 1
                Worksheets("aux").Activate
       
                     For Each cell In rngg
   
                          If cell <> "" Then
                            Cells(3, y) = cell
                            y = y + 1
                          End If
   
                     Next cell
 
  rngg2.SpecialCells(xlCellTypeConstants).Select
 
  NNewArray = Array(Selection.Value)
 
        .Sheets(NNewArray).Copy
       
    End With
    TempWindow.Close

End Sub

 

[Mephistopheles86]

any help please? or is this something i can't do?

 

[RoryA]

Please don't bump your thread that quickly - it actually doesn't help you anyway as it takes your thread out of the unanswered thread list that some members check when they first come onto the forum.

Anyway, your approach isn't going to work like that, but it would be simpler to create an array of the sheets as you go instead of populating a range at all, unless you need that for other purposes?

 

[Jon Peltier]

Note that you can only copy one sheet at a time that has a table, so you would have to loop through the array (as opposed to copying the whole array of sheets).