Duplicate a button to have the same functionality in a different excel sheet

Question

The code essentially duplicates data, creates a button, and transfers associated VBA code to a new workbook, allowing you to save the new workbook with the same functionality as the original workbook.

Copies data from columns A to AK in the "Hub" worksheet of the original workbook.

Creates a new workbook (wb2) and pastes the copied data into its first sheet.

Creates a new button in the new workbook (wb2) with the caption "Save the File."

Assigns the macro "CommandButton1_Click" to the newly created button. This macro is supposed to be in the original workbook.

Exports the VBA code associated with the original button from the original workbook and saves it as "ExportedCode.bas" in a specified SharePoint location.

Imports the exported VBA code ("ExportedCode.bas") into the VBA project of the new workbook (wb2).

Saves the new workbook with a specified file name and format (macro-enabled workbook - .xlsm) in the same SharePoint location.

Herer is the code I keep getting

Run Time error 50035: Method of export of object '__VBComponents' failed

Sub CommandButton1_Click()
    
    Dim wb1 As Workbook
    Dim ws1 As Worksheet
    Dim wb2 As Workbook
    Dim filePath As String
    Dim fileName As String
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("Hub")
    
    filePath = ""C:\Users\elyon\OneDrive\Desktop\TRYING PLEASE\""
    
    fileName = Format(ws1.Range("B2"), "DD-MMM-YYYY") & "-" & ws1.Range("C2") & ".xlsm"
    
    ws1.Range("A:AK").Copy
    
    Set wb2 = Workbooks.Add
    
    With wb2.Sheets(1).Range("A1") ' Specify the destination cell for pasting
    
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    
    End With
    
  ' Create a new button in wb2
    Dim newButton As Object
    Set newButton = wb2.Sheets(1).Buttons.Add(10, 10, 100, 30)
    newButton.Name = "CommandButton" ' Change the button name if desired
    newButton.Caption = "Save the File" ' Change the button caption if desired
    
    ' Assign the macro to the new button (replace "YourOriginalMacroName" with the actual macro name)
    newButton.OnAction = "CommandButton1_Click"
    
    ' Export the VBA code associated with the original button from the original workbook
    wb1.VBProject.VBComponents("Sheet1").Export filePath & "ExportedCode.bas"
    
    ' Import the VBA code into the new workbook
    wb2.VBProject.VBComponents.Import filePath & "ExportedCode.bas"
  
  
    Application.DisplayAlerts = False
    
    wb2.SaveAs fileName:=filePath & fileName, FileFormat:=52
    
    Application.DisplayAlerts = True
    
    wb2.Close
    
End Sub

Can someone help me fix this? Thank you

JohnM · Answer 1 · 2023-08-25T12:45:02.677

The error is because the place you are trying to save the exported Module to is not valid. Two most likely reasons:

In the code sample in your question, you have double-speech marks around the file path string i.e. filePath = ""C:\Users\elyon\OneDrive\Desktop\TRYING PLEASE\"" should be filePath = "C:\Users\elyon\OneDrive\Desktop\TRYING PLEASE\"
Having said that, that might be just a typo in your question and the real problem is likely to be that the 'TRYING PLEASE' folder does not exist on your Desktop (or that is the wrong path to your Desktop)

Duplicate a button to have the same functionality in a different excel sheet

1 Answers1