Help Selecting Sheets Array form a list box

[nelly]

Hi All

I am having trouble and tried many things but just can't work it out!

I have a list box that contains the names of 25 sheets in a worksbook that
contains 27 sheets. What I need is the code so that when you select a few
sheet names from the list box these sheets are selected and copied to a new
workbook.

I can get the sheet names into a string but cannot use that in an array!!!

Many Thanks in advance
Nelly

 

[Patrick Molloy]

in this example the user will have selected a number of sheetnames listed in
Listbox1, then they click the command button ...

Private Sub CommandButton1_Click()
Dim index As Long
Dim ar() As String
Dim count As Long
With ListBox1
For index = 0 To .ListCount - 1
If .Selected(index) Then
count = count + 1
ReDim Preserve ar(1 To count)
ar(count) = .List(index)
End If
Next
End With
Worksheets(ar).Copy
End Sub

 

[nelly]

Thankyou for this I had searched the internet and lots of codes had come up
similar to this but just could not fit them in and get them to work. any
chance you could put explanations as to what is happening after each line as
I cant realy get my head round what is happening?

Thanks again
Nelly

 

[Patrick Molloy]

see *** below
hopefully it makes sense!

best regards

Thankyou for this I had searched the internet and lots of codes had come
up
similar to this but just could not fit them in and get them to work. any
chance you could put explanations as to what is happening after each line
as
I cant realy get my head round what is happening?

Thanks again
Nelly

*** WITH object just makes the code run better/faster ...and sometimes
clearer to read*** LISTCOUNT is the number of items in the listbox, and
*** as its zero based, the first item is number 0 and the last is the
count-1*** if an item is selected, we need to add it to our array
*** so increment the couner*** REDIM allows us to increase the size of the array
*** usually deletes everything, so we use PRESERVE to maintian
*** any data therein alreadt*** the item selected in the listbox is found by .List(index)
*** so we add this to the array, in the newly created position*** worksheets(ar) is really this ...
*** Worksheets(Array("xx","xx"[,"xxx"]...) where xx are the elements in the
array ar
*** copying the selected sheets without a destination creates them in a new
workbook