I am using Office 2003 on Windows XP.

Situation:
The user has more than one sheet selected.

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Could someone please post an efficient method of obtaining this?
Thanks much in advance.

One way:

Dim i As Long
With ActiveWindow.SelectedSheets
For i = 1 To .Count
Cells(i, 1).Value = .Item(i).Name
Next i
End With


In article ,
quartz wrote:

I am using Office 2003 on Windows XP.

Situation:
The user has more than one sheet selected.

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Could someone please post an efficient method of obtaining this?
Thanks much in advance.

Hi JE,

One way:

Dim i As Long
With ActiveWindow.SelectedSheets
For i = 1 To .Count
Cells(i, 1).Value = .Item(i).Name
Next i
End With

Wouldn't that put the list in all selected sheets (since they are
grouped)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

No, it would place the entry in the activesheet. VBA doesn't support
processing sheets as a group for the most part. This is one of those parts.

--
Regards,
Tom Ogilvy

"Jan Karel Pieterse" wrote in message
...
Hi JE,

One way:

Dim i As Long
With ActiveWindow.SelectedSheets
For i = 1 To .Count
Cells(i, 1).Value = .Item(i).Name
Next i
End With

Wouldn't that put the list in all selected sheets (since they are
grouped)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

In article ,
Jan Karel Pieterse wrote:

Wouldn't that put the list in all selected sheets (since they are
grouped)?

True.

In article ,
JE McGimpsey wrote:

In article ,
Jan Karel Pieterse wrote:

Wouldn't that put the list in all selected sheets (since they are
grouped)?

True.

Danged Send Key!

True. They're grouped. But VBA can't write to more than one sheet at a
time.

Hi JE,

True. They're grouped. But VBA can't write to more than one sheet at a
time.

I suspected that, but was too lazy to test myself <g.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Jan is right of course, but I figured it out...I could not come up with
the "ActiveWindow.SelectedSheets" part. Thanks for the help.

"JE McGimpsey" wrote:

One way:

Dim i As Long
With ActiveWindow.SelectedSheets
For i = 1 To .Count
Cells(i, 1).Value = .Item(i).Name
Next i
End With


In article ,
quartz wrote:

I am using Office 2003 on Windows XP.

Situation:
The user has more than one sheet selected.

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Could someone please post an efficient method of obtaining this?
Thanks much in advance.

Hi Quartz,

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Sub test()
Dim osh As Object
Dim sSheetnames() As String
ReDim sSheetnames(1)
Dim lCount As Long
For Each osh In ActiveWindow.SelectedSheets
lCount = lCount + 1
ReDim sSheetnames(lCount)
sSheetnames(lCount) = osh.Name
Next

End Sub


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Perfect, thanks a lot!

"Jan Karel Pieterse" wrote:

Hi Quartz,

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Sub test()
Dim osh As Object
Dim sSheetnames() As String
ReDim sSheetnames(1)
Dim lCount As Long
For Each osh In ActiveWindow.SelectedSheets
lCount = lCount + 1
ReDim sSheetnames(lCount)
sSheetnames(lCount) = osh.Name
Next

End Sub


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Look at my first post

I think Jan forgot the Redim PRESERVE
which expands the array WITHOUT deleting the existing information

"quartz" wrote:

Perfect, thanks a lot!

"Jan Karel Pieterse" wrote:

Hi Quartz,

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Sub test()
Dim osh As Object
Dim sSheetnames() As String
ReDim sSheetnames(1)
Dim lCount As Long
For Each osh In ActiveWindow.SelectedSheets
lCount = lCount + 1
ReDim sSheetnames(lCount)
sSheetnames(lCount) = osh.Name
Next

End Sub


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

OK, OK and I forgot the selected sheets part of the question.
Add Preserve to jan's reDim code

"Vacation's Over" wrote:

Look at my first post

I think Jan forgot the Redim PRESERVE
which expands the array WITHOUT deleting the existing information

"quartz" wrote:

Perfect, thanks a lot!

"Jan Karel Pieterse" wrote:

Hi Quartz,

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Sub test()
Dim osh As Object
Dim sSheetnames() As String
ReDim sSheetnames(1)
Dim lCount As Long
For Each osh In ActiveWindow.SelectedSheets
lCount = lCount + 1
ReDim sSheetnames(lCount)
sSheetnames(lCount) = osh.Name
Next

End Sub


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Vacation's,

I think Jan forgot the Redim PRESERVE

Yup, well spotted.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Function aryAirCode()as Variant

dim wks as Worksheet
Dim counter as long
Dim buf() as String

conter = 1

For each wks in Activeworkbook.Sheets
redim preserve buf(1 to counter)
buf(counter) = wks.Name
counter = counter+1
next wks

aryAirCode = buf
end function

sub YOURCODEBLOCK()
dim Your variable for the array as Variant
'yourcode
Your variable for the array = aryAirCode()
'yourcode

end YOURCODEBLOCK sub


"quartz" wrote:

I am using Office 2003 on Windows XP.

Situation:
The user has more than one sheet selected.

Programmatically I need:
An array of one column by multiple rows of the names of all the selected
sheets.

Could someone please post an efficient method of obtaining this?
Thanks much in advance.