VBA Array

[kazaar69]

Hi Shifus,
I have a little problem trying to set up a dynamic array in VBA, tried googling but did not manage to find any solution. Hope someone can help or guide me in the right direction.

I need to set up a dynamic array consisting of:

1) Sheet(A) to Sheet(G) and <--Variable
2) Sheet(Y) and (Z) <--Constant

Problem is I may not always have Sheet(A) to Sheet(G), it may be Sheet(A), Sheet(B) and Sheet(G) only and Sheet(Y) and (Z) or Sheet(C), Sheet(D) and Sheet(E) only and Sheet(Y) and (Z).

The reason for setting up a dynamic array is because I need to copy them together into a new workbook and I have to copy them together becoz there are formulas in (Y) and (Z).

Thank you in advance!

 

[addats]

ReDim Statement (Visual Basic)

 

[kazaar69]

ReDim Statement (Visual Basic)

I can define the original set of array from (A) to (G) but how do I redim it to only worksheets that are available?

MyArray = Array(A, B, C, D, E, F, G)

Here is where i need to redefine the new array but it doesn't seem to be able to 'understand' my ShtName as an array.

CountWsRev = ActiveWorkbook.Sheets.Count

For i = 1 To CountWsRev

Select Case Sheets(i).Name

Case Is = "A", "B", "C", "D", "E", "F", "G"

If i = 1 Then
ShtName = "" & Sheets(i).Name & ""
Else

ShtName = ShtName & "," & "" & Sheets(i).Name & ""


End If



End Select

Next i

Sheets(Array(ShtName)).Copy

 

[addats]

The sheet.copy does not accept the array.
use this instate

How to copy or move sheets by using VBA code in Excel for Mac

 

[kazaar69]

The sheet.copy does not accept the array.
use this instate

How to copy or move sheets by using VBA code in Excel for Mac

It works if the following is used:

Sheets(Array("A", "B", "C", "D", "E", "F", "G")).Copy

 

[kazaar69]

Upz

 

[davidktw]

I don't think you will have variables at worksheet level. Read up this
Excel VBA Variables Scope and Lifetime. Scope and Lifetime of Excel VBA Variables

In fact, I would suppose what you want is react to worksheet creation and do whatever you want to do there.

Below is a screen capture of what I did as a sample

Output ?