VBA export module to new workbook

[toughie]

Hello,

looking for a little help with this code,

Private Sub CommandButton2_Click()
Application.ActiveWorkbook.SaveAs "C:\Users\andy_\OneDrive\Desktop\VAMLog" & Range("G5") & "-" & "OP" & TextBox5.Text
SourceWB.VBProject.VBComponents("SAVEPDF").Export "SAVEPDF.frm"
DestinationWB.VBProject.VBComponents.Import "SAVEPDF.frm"

End Sub

when I click the command button I want the userform to be copied along to the new workbook but I get an error cant seem to suss it out

all help appreciated thanks

 

[rlv01]

when I click the command button I want the userform to be copied along to the new workbook

If 'ActiveWorkbook' has userforms then those forms are already retained in the new workbook created by 'SaveAs' will have those forms as well, so that implies you are trying to do something different and maybe more explanation is needed. A good start would be explaining the differences between the 3 workbooks your code references

ActiveWorkbook
SourceWB
DestinationWB​

and how they relate to each other. It seems odd to me that if you wanted to add a form to the activeworkbook being saved that you would have the export/import lines of code after the 'SaveAs' operation.

but I get an error cant seem to suss it out

That is too vague for anyone to help you with. There are hundreds possible runtime errors. You should always try to provide two pieces of info when asking for help with an error:

1. Specific error number and/or description
2. Specific line of code that throws the error.​

To help with code clarity, try to use code tags when posting code

1. Paste your VBA code into the text area
2. Highlight (select) the code
3. Cick the "#" button.
​

This will wrap your texts in code tags which will display it more clearly

 

[toughie]

Yeah sorry for being vague, i think my problem is i am not saving as until after the new worksheet is created so therefore does not transfer over? can i transfer the form over without saving as?

 

[toughie]

 Set wb = Workbooks.Add    ThisWorkbook.Activate
    ActiveSheet.Copy Before:=wb.Sheets(1)
    wb.Activate
    SAVEPDF.Show vbModeless
    Me.StartUpPosition = 0
    Me.Top = 0
    Me.Left = 0

This code copies the actie worksheet to a new workbook and opens up the SAVEPDF userform but i need it to export the userform along with it

 

[rlv01]

The other way to code this is to delete all worksheets in the workbook except the one you want to save and then do a .SaveAs to a new workbook. That will preserve the UserForm

 

[toughie]

I am unable to do that because the other works sheets are used for the first userfrom

 

[toughie]

Is there a way i could attach the userform to the individual sheets in the book so if the sheets are then moved to another book they could show the userform from there

 

[rlv01]

I am unable to do that because the other works sheets are used for the first userfrom

I don't really know what you mean by the 'first' userform because so far you have only mentioned one userform, not multiple userforms. If the userform you want to move (export/import, whatever) relies on other worksheets, besides the one you want to save to a new file, then you will never be able to move the userform to a new workbook without also moving those other sheets as well or the userform will not function. However if the userform you want to move is the 'second' userform and it only relies on that one worksheet you want to move to a new workbook, then the method I mentioned (deleting sheets and doing a SaveAs) will work. I suspect you might be getting bogged down in the the XY Problem and there is probably another way to accomplish your final goal.

 

[mikerickson]

The issue that I see in the OP code is that the first line creates a new workbook with SaveAs.
The two workbook variables, SourceWB and DestinationWB, are set before the creation of the new workbook, so the .Import line doesn't act on the newly SaveAs'd book.

 

[toughie]

my apologies, ill run through my code

- excel initiates with a userform
-user fills out form and this then generates and new workbook
- at this point I want the new workbook to open and save the second userform to the newly created workbook( second userform is only saved to initial workbook)
- the newly created workbook can then be accessed at anytime with the second userform saved to the workbook

Im probably making this way harder than it needs to be haha

many thanks