Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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-- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... 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-- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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" wrote: 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-- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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--- -- RyGuy "JLGWhiz" wrote: 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" wrote: 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-- -- RyGuy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... 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--- -- RyGuy "JLGWhiz" wrote: 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" wrote: 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-- -- RyGuy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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--- -- RyGuy "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... 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--- -- RyGuy "JLGWhiz" wrote: 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" wrote: 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-- -- RyGuy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy UserForm to new Workbook
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.xl s") 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 "ryguy7272" wrote in message ... 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--- -- RyGuy "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... 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--- -- RyGuy "JLGWhiz" wrote: 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" wrote: 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-- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy UserForm | Excel Programming | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
Copy a userform to a different workbook with VBA | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming |