fill array sheets

[sal21]

I need to fill this array dinamicly and not with a fixed name of sheets...
In effect i want to fill it with all sheets from source workbook but exclude sheet1, sheet3 and sheets6, possible?
other way are welcome, sure.

Code: Select all

With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("ReadMe", "MailSheet(s)")).Copy
    End With

[HansV]

You can use code like this:

Code: Select all

Dim n As Long
Dim arrSheets() As String
...
n = ...
ReDim arrSheets(1 To n)
' You can use variables or expressions instead of literal strings here
arrSheets(1) = "This Sheet"
arrSheets(2) = "That Sheet"
...
With SourceWB
  ...
  .Sheets(arrSheets).Copy
End With

[sal21]

You can use code like this:

Code: Select all

Dim n As Long
Dim arrSheets() As String
...
n = ...
ReDim arrSheets(1 To n)
' You can use variables or expressions instead of literal strings here
arrSheets(1) = "This Sheet"
arrSheets(2) = "That Sheet"
...
With SourceWB
  ...
  .Sheets(arrSheets).Copy
End With

hi my frined, this is my test code... but have error

Code: Select all

    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
N = 1
ReDim arrSheets(1 To 5)

 For Each WS In Worksheets
        If WS.Name <> "MATRICE" And WS.Name <> "REPORT" And WS.Name <> "TEST" And WS.Name <> "Legenda" Then
                arrSheets(N) = WS.Name
                N = N + 1
        End If
        Next
          [b].Sheets(arrSheets).Copy[/b]    End With

[HansV]

You specify that arrSheets has 5 elements, so if the workbook has more sheets, the code will fail with the error message "Index out of bounds".
And in the line For Each WS In Worksheets you don't tell VBA that the sheets belong to SourceWB.

Change the code to

Code: Select all

  With SourceWB
    Set TheActiveWindow = ActiveWindow
    Set TempWindow = .NewWindow
    For Each WS In .Worksheets
      If WS.Name <> "MATRICE" And WS.Name <> "REPORT" And _
          WS.Name <> "TEST" And WS.Name <> "Legenda" Then
        ReDim Preserve arrSheets(N)
        arrSheets(N) = WS.Name
        N = N + 1
      End If
    Next WS
    .Sheets(arrSheets).Copy
  End With

[sal21]

You specify that arrSheets has 5 elements, so if the workbook has more sheets, the code will fail with the error message "Index out of bounds".
And in the line For Each WS In Worksheets you don't tell VBA that the sheets belong to SourceWB.

Change the code to

Code: Select all

  With SourceWB
    Set TheActiveWindow = ActiveWindow
    Set TempWindow = .NewWindow
    For Each WS In .Worksheets
      If WS.Name <> "MATRICE" And WS.Name <> "REPORT" And _
          WS.Name <> "TEST" And WS.Name <> "Legenda" Then
        ReDim Preserve arrSheets(N)
        arrSheets(N) = WS.Name
        N = N + 1
      End If
    Next WS
    .Sheets(arrSheets).Copy
  End With

sal.