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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

see *** below
hopefully it makes sense!

best regards




"nelly" wrote in message
...
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" wrote:

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()
*** set memeory for variables
Dim index As Long
Dim ar() As String
Dim count As Long
*** WITH object just makes the code run better/faster ...and sometimes
clearer to read
With ListBox1
*** 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
For index = 0 To .ListCount - 1
*** if item, selected(index) has been selected, then this returns TRUE
If .Selected(index) Then
*** if an item is selected, we need to add it to our array
*** so increment the couner
count = count + 1
*** REDIM allows us to increase the size of the array
*** usually deletes everything, so we use PRESERVE to maintian
*** any data therein alreadt
ReDim Preserve ar(1 To count)
*** the item selected in the listbox is found by .List(index)
*** so we add this to the array, in the newly created position
ar(count) = .List(index)
End If
Next
End With
*** 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
Worksheets(ar).Copy
End Sub

"nelly" wrote in message
...
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