Solved: Creating a number of workbooks

[nickirvine]

Hi,

I wonder if anyone can help me on this one.

I am trying to create a piece of VB code that from within an Excel spreadsheet creates a new work book and deletes the sheets that I select from the copy.

To explain more...

I have a workbook, with 4 sheets:
sheet A, sheet B, Sheet C, Sheet D

From this workbook I open a userform which states: "Reproduce workbook, new name__________" (i enter new name here e.g "book1" which becomes the new filename)

On the userform it lists: sheet A, sheet B, sheet c, sheet d with tickboxes next to them.

I click the tick box next to sheet A and sheet B. So it creates a workbook called "book1" with copies of sheet A and sheet B. Then it takes me back to the form so i can do another one.

I need all the VB code currently on sheet A and sheet B to transfer over to the new workbook.

Is this possible? thanks for any help you can offer me.

Nick

[Simon Lloyd]

A sample workbook......etc would be a good start

[Bob Phillips]

Why don't you just copy the whole workbook, delete the sheets you DON'T want, the code should go with it then

[nickirvine]

I want to repeat this process 80 times, so by selecting what I wanted to keep for each one it would be a lot quicker.

I havent really started the workbook, as I'm not sure if it is possible. But here is an example of what I'm trying to create.

Press the command button to open the form.

[Bob Phillips]

I want to repeat this process 80 times, so by selecting what I wanted to keep for each one it would be a lot quicker.

No it wouldn't.

[Bob Phillips]

[vba]

Private Sub CommandButton1_Click()

Dim SheetNames As String
Dim sh As Worksheet

ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

If Me.TextBox1.Value = "" Then

MsgBox "No filename"
Exit Sub
End If

If Not Me.CheckBox1.Value And Not Me.CheckBox2.Value And Not Me.CheckBox3.Value Then

MsgBox "No sheets selected"
Exit Sub
End If

Application.DisplayAlerts = False

With ActiveWorkbook

.Worksheets("Sheet1").OLEObjects("CommandButton1").Delete
If Not Me.CheckBox1 Then .Worksheets("Sheet1").Delete
If Not Me.CheckBox2 Then .Worksheets("Sheet2").Delete
If Not Me.CheckBox3 Then .Worksheets("Sheet3").Delete
End With

Application.DisplayAlerts = True

ActiveWorkbook.SaveAs TextBox1.Text
End Sub
[/vba]

[nickirvine]

Thanks for your help. It does copy the worksheets but it doesn't carry the vb over to the new file or the command button on the worksheet.

Would it be better to copy the workbook then delete the sheets not selected?

[Bob Phillips]

It does copy the VBA,I tested it.

It doesn't copy the button, I assumed that would not be wanted so I deleted it.

[nickirvine]

Hi,

Ah sorry, I can see the code to delete the button.

It does copy the VB but not the VB UserForm. Is it possible to copy the userforms in the workbook aswell?

Thanks,

Nick

[Simon Lloyd]

You can always add a CALL to a sub and use this:[VBA]Sub Exp_Imp_Form()
Dim MyWb As Workbook
Dim NewWb As Workbook
Const sStr As String = "C:\MyFile.frm"
Set MyWb = Workbooks("MyBook1.xls")
Set NewWb = Workbooks("MyBook2.xls")
MyWb.VBProject.VBComponents("Userform1").Export _
Filename:=sStr
NewWb.VBProject.VBComponents.Import _
Filename:=sStr
Kill sStr
End Sub[/VBA]

[nickirvine]

Thanks.

I have the easiest way for my situation is to save the userforms on then just use the vb to import them using

[VBA]Application.VBE.ActiveVBProject.VBComponents.Import "C:\mtfrm.frm"[/VBA]

Thanks for your help.

[Bob Phillips]

Thanks.

I have the easiest way for my situation is to save the userforms on then just use the vb to import them using

[VBA]Application.VBE.ActiveVBProject.VBComponents.Import "C:\mtfrm.frm"[/VBA]

Thanks for your help.

That is what Simon said to you

As for easiest, is there another way?

[Simon Lloyd]

Do you think that this would be better distibuted as an addin?

[Bob Phillips]

Do you think that this would be better distibuted as an addin?

Is that at me or the OP Simon?

[Simon Lloyd]

The OP Bob, it seems to me that that would be the way to go if distributing code through 30+ workbooks!

[Bob Phillips]

It seems to me that the OP already has a single source for the code, so an addin probably won't help much. I was just querying his statement because he seemd to intimate he had solved it, when he did the same as you had suggested.

[Simon Lloyd]

Ok, well the only future problem i can see is Trusted access to the VBE when eventually it gets protected.

{just as a side while you're reading this, did you see my post in the SUMPRODUCT forum? it still has me stumped!)

[Bob Phillips]

{just as a side while you're reading this, did you see my post in the SUMPRODUCT forum? it still has me stumped!)

Just replied!