Array of all selected sheet names?

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 <[email protected]>,
quartz <[email protected]> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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.

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 <[email protected]>,
> quartz <[email protected]> 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.
>

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
> >
> >

In article <[email protected]>,
Jan Karel Pieterse <[email protected]> wrote:

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

True.

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> In article <[email protected]>,
> Jan Karel Pieterse <[email protected]> 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.

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 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

Hi Vacation's,

> I think Jan forgot the Redim PRESERVE

Yup, well spotted.

Regards,

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