PDA

View Full Version : Listbox worksheet help



Northender
03-26-2007, 08:14 AM
Hello,

I am a newbie so please stay with me on this..

I am trying to use a listbox of worksheets to create a new workbook with only those sheets in it. Listbox1 has a list of specific worksheets ( just a range in one sheet ). You then select the ones you want and these transfer to listbox2. I then want to have buttons to either print or copy these worksheets into a new workbook. My problem is how do I take the values in listbox2 and transfer them as values to use as worksheet names. I beleive it will be along the lines of " for each x in list then add worksheet.name(x) or am I completly wrong..:think:

lucas
03-26-2007, 08:28 AM
Please post your workbook with what you have so far so we don't have to create it from scratch

Bob Phillips
03-26-2007, 08:32 AM
Make sure you set the Listbox's multiselect property Private Sub cmdOK_Click()
Dim oWB As Workbook
Dim cSheets As Long
Dim i As Long
Dim first As Boolean
cSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oWB = Workbooks.Add
Application.SheetsInNewWorkbook = cSheets
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
oWB.Worksheets.Add(after:=oWB.Worksheets(oWB.Worksheets.Count)).Name = .List(i)
End If
Next i
End With
Application.displayalerte = False
oWB.Worksheets(1).Delete
Application.DisplayAlerts = True
End Sub

Private Sub cmdQuit_Click()
Me.Hide
End Sub

Private Sub UserForm_Activate()
End Sub
Dim sh As Worksheet
With Me.ListBox1.Clear
For Each sh In ActiveWorkbook.Worksheets
.AddItem sh.Name
Next sh
End With
End Sub

Edit Lucas: Fixed(I hope)..let me know if I missed anything Bob

Northender
03-26-2007, 08:37 AM
Attached is the userform. I have put this into a new sheet as my other book is 5+mb.

Thanks

Bob Phillips
03-26-2007, 08:47 AM
My example show you how to create a new workbook with the selected values. That way you don't need a second listbox, you can do it all off of the first one.

Bob Phillips
03-26-2007, 08:48 AM
Also, a bit difficult to test as your workbook doesn't have sheets with the values in the Listbox.

Northender
03-26-2007, 08:55 AM
Thanks for the replies. I beleive I understand the code to apply to my sheet.

Thanks again for the swift responce, much appreciated :thumb

Northender
03-27-2007, 01:52 AM
Sorry to keep this going, but could you just help with the code that I need to unsert to copy the actual data from the sheets across to the new workbook. I want to do this as pastespecial to remove all the formulas, but keep the format.

Bob Phillips
03-27-2007, 01:59 AM
Use the macro recorder and do it manually, you should see what is required then.

Northender
03-27-2007, 05:18 AM
I just dont seem to be able to get my head around this.. This is part of the code supplied, with what I am tring to do. the problem is that when I run this, it coppies the sheets into seperate workbooks and doesnt remove the formulas etc. I understand this, but cannot work out how to incorporate it into the add after worksheet...

Application.SheetsInNewWorkbook = cSheets
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
oWB.Worksheets.Add(after:=oWB.Worksheets(oWB.Worksheets.Count)).Name = .List(i)
cWB.Worksheets(.List(i)).Copy
'oWB.Worksheets(.List(i)).pastespecial Paste:=xlValues
'oWB.Worksheets(.List(i)).pastespecial Paste:=xlFormats
End If
Next i
End With