selecting multiple sheets by use of an array?
 

Hello world!

Ok, I have an array with values that equal some names of worksheets i
my workbook. I want to use this array to select these worksheets an
paste them into a new workbook. So my VBA works for the array values
but I don’t know the syntax to select the sheets based on the arra
value and then paste it. I used a record marco to see how it’s done
if I manually hold the <Ctrl down and select the particular sheets.
Here’s what I got:

Sub array_tester()

Dim arrayList(0 To 12) As String
Dim pointer As Integer

pointer = 0

For CounterComp = 6 To 15

IncludeSheet = Worksheets("Labels").Cells(CounterComp, 31)
DataSheet = Worksheets("Labels").Cells(CounterComp, 7)

If IncludeSheet = True Then
arrayList(pointer) = DataSheet
pointer = pointer + 1

Else
pointer = pointer + 1

End If

Next CounterComp

Sheets(Array(arraylist)).Select ‘Here’s where it doesn’t work for me…
Selection.COPY

Any help would be appreciated…Thanks

--
Message posted from http://www.ExcelForum.com

selecting multiple sheets by use of an array?
 

One way:

Dim vArr As Variant
Dim i As Long
vArr = Array("Sheet2", "Sheet3", "Sheet4")
Sheets(vArr(LBound(vArr))).Select
For i = LBound(vArr) + 1 To UBound(vArr)
Sheets(vArr(i)).Select Replace:=False
Next i
ActiveWindow.SelectedSheets.Copy


In article ,
anhjan wrote:

Hello world!

Ok, I have an array with values that equal some names of worksheets in
my workbook. I want to use this array to select these worksheets and
paste them into a new workbook. So my VBA works for the array values,
but I don’t know the syntax to select the sheets based on the array
value and then paste it. I used a record marco to see how it’s done,
if I manually hold the <Ctrl down and select the particular sheets.
Here’s what I got:

Sub array_tester()

Dim arrayList(0 To 12) As String
Dim pointer As Integer

pointer = 0

For CounterComp = 6 To 15

IncludeSheet = Worksheets("Labels").Cells(CounterComp, 31)
DataSheet = Worksheets("Labels").Cells(CounterComp, 7)

If IncludeSheet = True Then
arrayList(pointer) = DataSheet
pointer = pointer + 1

Else
pointer = pointer + 1

End If

Next CounterComp

Sheets(Array(arraylist)).Select ‘Here’s where it doesn’t work for me…
Selection.COPY

Any help would be appreciated…Thanks!


---
Message posted from http://www.ExcelForum.com/

selecting multiple sheets by use of an array?
 

You my friend, Are a genius!!!

Thank you very much!!

--
Message posted from http://www.ExcelForum.com

selecting multiple sheets by use of an array?
 

And you don't even need to select them:

Dim vArr As Variant
vArr = Array("Sheet2", "Sheet3", "Sheet4")
Sheets(vArr).Copy



"anhjan <" wrote:

Hello world!

Ok, I have an array with values that equal some names of worksheets in
my workbook. I want to use this array to select these worksheets and
paste them into a new workbook. So my VBA works for the array values,
but I don’t know the syntax to select the sheets based on the array
value and then paste it. I used a record marco to see how it’s done,
if I manually hold the <Ctrl down and select the particular sheets.
Here’s what I got:

Sub array_tester()

Dim arrayList(0 To 12) As String
Dim pointer As Integer

pointer = 0

For CounterComp = 6 To 15

IncludeSheet = Worksheets("Labels").Cells(CounterComp, 31)
DataSheet = Worksheets("Labels").Cells(CounterComp, 7)

If IncludeSheet = True Then
arrayList(pointer) = DataSheet
pointer = pointer + 1

Else
pointer = pointer + 1

End If

Next CounterComp

Sheets(Array(arraylist)).Select ‘Here’s where it doesn’t work for me…
Selection.COPY

Any help would be appreciated…Thanks!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson