Copy UserForm to new Workbook

[ryguy7272]

I am using Ron DeBruin's code to tear off sheets and append a specific sheet
to each sheet that is torn off. I wanted to mail these tiny Workbooks to
several people, but they need a certain macro in the original workbook to
fill out some UserForms and email the sheets back to me. When I create the
tear off sheets, the none of the macros follow the copied/saved sheets and
none of the UserForms follow the tear off sheets either. I guess I can use
Private Subs and those will be copied several times to the new Workbooks,
right. There are six UserForms, all named UserForm1-UserForm6. Is there a
way to force those UserForms to be copied to each new Workbook that is
created?

Regards,
Ryan--

 

[Ron de Bruin]

Hi Ryan

Maybe it is a good idea to create a workbook with the userforms and all your code(template)
With code we can open this workbook each time and copy a sheet in it and save/mail it

Need to know what code you use now so I can make a example

 

[Nigel]

The simplest option would be to create a template containing the six
userforms and any code and sheet structure formatting you require, open a
new workbook based on the template and copy in data from the main workbook.
Repeat as required.

 

[JLGWhiz]

Hi Ryan, The easy way to do it is to open your master workbook and a tear
off workbook at the same time. Then open the VB editor. Find the UserForm
in the master workbook project listing that you want to copy to the tear off,
left click and hold then drag and drop to the tear off workbook project
listing. It takes the code
right along with it.

 

[ryguy7272]

I thought I posted the code; guess not:
http://www.rondebruin.nl/copy6.htm

So, this works fine! Thanks a ton, Ron!! I know I can click/drag UF and
Modules into new books, but is there a way to identify each UF and each
Module, using code, and copy/paste those into the new Workbooks when running
the macro that I referenced above. Or, is using a template the only option?


Regards,
Ryan---

 

[Ron de Bruin]

I wanted to mail these tiny Workbooks to several people

Where can we find the mail addresses ?
Do you have the address in each worksheet ?

 

[ryguy7272]

I mail it to about 9-10 people, all listed in a Range in Excel, using your
code:
http://www.rondebruin.nl/mail/folder3/message.htm

They fill out some information on a single SS, using a few UserForms, and
mail it back to me:
http://www.rondebruin.nl/mail/folder2/mail2.htm


All the data is stored in Access. I created a work-around, using Excel,
because many people in my office do not have MS Access, or do not know how to
use Access.

I am thinking the 'Template' option may be the way to go with this. Hoever,
I think I will have to scrap the whole 'Create a workbook from every
worksheet in your workbook' idea (which, by the way, I love):
http://www.rondebruin.nl/copy6.htm

Any other ideas?


Thanks so much,
Ryan---

 

[Ron de Bruin]

Try this test macro

Create a workbook with all the userforms and code you want
Change the path/file name in this line
Set Destwb = Workbooks.Open("C:\Users\Ron\Documents\template.xls")
Be sure that this file is not open when you run the macro

Be sure that this workbook have the same file format as the workbook with the code
If it is working OK you can add the mail code in the same macro

Sub Copy_Every_Sheet_To_New_Workbook_Test()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook

'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName

'Copy every visible sheet to a new workbook
For Each sh In Sourcewb.Worksheets

'If the sheet is visible then copy it to a new workbook
If sh.Visible = -1 Then
Set Destwb = Workbooks.Open("C:\Users\Ron\Documents\Book5.xlsm")

sh.Copy after:=Destwb.Worksheets(Destwb.Worksheets.Count)


'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
If Sourcewb.Name = .Name Then
MsgBox "Your answer is NO in the security dialog"
GoTo GoToNextSheet
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

'Save the new workbook and close it
With Destwb
.SaveAs FolderName _
& "\" & sh.Name & FileExtStr, _
FileFormat:=FileFormatNum
.Close False
End With

End If
GoToNextSheet:
Next sh

MsgBox "You can find the files in " & FolderName

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I mail it to about 9-10 people, all listed in a Range in Excel, using your
code:
http://www.rondebruin.nl/mail/folder3/message.htm

They fill out some information on a single SS, using a few UserForms, and
mail it back to me:
http://www.rondebruin.nl/mail/folder2/mail2.htm


All the data is stored in Access. I created a work-around, using Excel,
because many people in my office do not have MS Access, or do not know how to
use Access.

I am thinking the 'Template' option may be the way to go with this. Hoever,
I think I will have to scrap the whole 'Create a workbook from every
worksheet in your workbook' idea (which, by the way, I love):
http://www.rondebruin.nl/copy6.htm

Any other ideas?


Thanks so much,
Ryan---