PDA

View Full Version : Solved: Creating a number of workbooks



nickirvine
08-03-2010, 06:17 AM
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
08-03-2010, 06:21 AM
A sample workbook......etc would be a good start :)

xld
08-03-2010, 06:51 AM
Why don't you just copy the whole workbook, delete the sheets you DON'T want, the code should go with it then

nickirvine
08-03-2010, 07:19 AM
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.

xld
08-03-2010, 07:27 AM
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.

xld
08-03-2010, 07:37 AM
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

nickirvine
08-03-2010, 08:01 AM
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?

xld
08-03-2010, 03:45 PM
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
08-04-2010, 01:51 AM
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
08-04-2010, 02:01 AM
You can always add a CALL to a sub and use this: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

nickirvine
08-04-2010, 02:29 AM
Thanks.

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

Application.VBE.ActiveVBProject.VBComponents.Import "C:\mtfrm.frm"

Thanks for your help.

xld
08-04-2010, 02:55 AM
Thanks.

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

Application.VBE.ActiveVBProject.VBComponents.Import "C:\mtfrm.frm"

Thanks for your help.

That is what Simon said to you :dunno

As for easiest, is there another way?

Simon Lloyd
08-04-2010, 04:19 AM
Do you think that this would be better distibuted as an addin?

xld
08-04-2010, 06:28 AM
Do you think that this would be better distibuted as an addin?

Is that at me or the OP Simon?

Simon Lloyd
08-04-2010, 08:55 AM
The OP Bob, it seems to me that that would be the way to go if distributing code through 30+ workbooks!

xld
08-04-2010, 09:47 AM
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
08-04-2010, 09:59 AM
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!)

xld
08-04-2010, 10:11 AM
{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!