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

 

[toughie]

I moved the source and destination lines to before the save as and I through up a not recognised error

 

[toughie]

Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy Before:=wb.Sheets(1)
wb.Activate
Workbooks("Copy of TEST2 - Copy.xlsm").Close
Dim path As String
Dim fileName As String
path = "C:\Users\andy_\OneDrive\Desktop\VAMLog"
fileName = "SAVEPDF.frm"
ThisWorkbook.VBProject.VBComponents.Import path & fileName

got a little further but still stuck

no errors but no userform saved to the new workbook?
​

 

[toughie]

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.

I tried this but still doesn't import the userform from a folder either

 

[rlv01]

    Dim path As String
    Dim fileName As String
    Dim wb As Workbook, wbs As Workbook

    Set wbs = ThisWorkbook                            'source workbook
    Set wb = Workbooks.Add                            'destination workbook

    wbs("NameOfYourWorksheetGoesHere").Copy Before:=wb.Sheets(1)
    
    path = "C:\Users\andy_\OneDrive\Desktop\VAMLog"
    fileName = "SAVEPDF.frm"    'SAVEPDF.frx must be at the same location

    wb.VBProject.VBComponents.Import path & "\" & fileName
    wb.SaveAs fileName:="YourNewFileNameGoesHere", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    wb.Close

 

[toughie]

perfect that has fixed it! thanks alot rlv01!

The final part of the workbook is to have a command button on the worksheet which i can click to open up the second userform at any time after the new workbook is saved, the problem is when i save the new worksheet and then open it again the click button doesnt work because it automatically changes the name of the macro

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][B]'2-OP.xlsm'![/B]Sheet4.CommandButton1_Click[/FONT]

the part highlighted in bold adds itself to the name of the macro

[LEFT][COLOR=#222222][FONT=Verdana]Sheet4.CommandButton1_Click[/FONT][/COLOR][/LEFT]

how do i stop it from automatically doing that?

many thanks in advance
​

 

[rlv01]

Instead of

Sheet4.CommandButton1_Click

try

Me.CommandButton1_Click

And if that does not work then you'll need to post the code for the whole complete subroutine where

Sheet4.CommandButton1_Click

is used so I can assess how you are using that statement.

 

[toughie]

Here is the sub routine,

Sub CommanndButton1_Click()
SAVEPDF.Show vbModeless
End Sub

It is attached to sheet 4 and all my other worksheets that use the command button

once the worksheet is saved i then try and use the command button but it cant run the macro because it renames itself automatically

 

[rlv01]

Here is the sub routine,

Sub CommanndButton1_Click()
SAVEPDF.Show vbModeless
End Sub

It is attached to sheet 4 and all my other worksheets that use the command button

once the worksheet is saved i then try and use the command button but it cant run the macro because it renames itself automatically

Something is missing. You have posted the subroutine for the button;but what you posted before

[B][COLOR="#0000FF"]Sheet4[/COLOR][/B].CommandButton1_Click

is not the button subroutine; but something in another macro that is CALLING the button click subroutine. That other macro what I was asking to see. But since your button click macro is only one line, then perhaps you can simply replace every instance of this

Sheet4.CommandButton1_Click

in the worksheet code module, with this:

SAVEPDF.Show vbModeless

 

[toughie]

Hi there,

it is not possible to name the Code

Sheet4.CommandButton1_Click()

the problem is when the file is renamed (saveas) the macro no longer runs because the macro name changes when the file is renamed

macro initial name - CommandButton1_Click
After new workbook is created - 'C:\Users\tougand\Desktop\vampro.xlsm'!CommanndButton1_Click - this then tries to open the master workbook

thanks for the support so far, just need the last piece of the puzzle !

 

[rlv01]

Reading the tea leaves, it looks to me that you might be using a form button on your copied worksheet to run the CommandButton1_Click macro. If so you should use an active-X button (a button control) instead.