Save and preserve VBA code on the new workbook

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
I have the following code that separates and creates a new workbook that does not include the sheets "Main", "template" and promotes the user to save it at a desired location and exits out of the old workbook and keeps the new workbook that was created open. The code works perfectly however, the new sheets that were created using the templates have buttons on them that do a certain function using the VBA code. is there anyway to preserve the VBA code so that its saved on the new workbook that was created?

I just want to be able to open the workbook later and still have the functions of the buttons to use.



Code:
Sub SaveWorkbook()

Dim ws As Worksheet
Dim savename As String
Dim cnt As Long
Dim arrSheets()


    With ActiveWorkbook
        ReDim arrSheets(1 To .Sheets.Count)
            For Each ws In .Sheets
            Select Case ws.Name
                Case "Main", "template"
                    ' do nothing
                Case Else
                    cnt = cnt + 1
                    arrSheets(cnt) = ws.Name
            End Select
        Next ws
        ReDim Preserve arrSheets(1 To cnt)
        ' copy sheets to new workbook
        .Sheets(arrSheets).Copy
    End With
       
    MsgBox ("You will now be prompted to save your file, please choose a location and name then click 'Save'") 'Notifies User
    savename = Application.GetSaveAsFilename(fileFilter:="Exel Files (*.xlsx), *.xlsx")
    If savename <> "False" Then
        ' save and close new workbook
        With ActiveWorkbook
            .SaveAs Filename:=savename, FileFormat:=51  'Something iswrong




        Workbooks("Level Material Workbook v1.4.xlsm").Close SaveChanges:=False




        End With
    End If




End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You could instead do SaveCopyAs, open the new workbook, and then delete sheets main and template.
 
Upvote 0
You could instead do SaveCopyAs, open the new workbook, and then delete sheets main and template.

I want someone to be able to use this worksheet with a click of a button so I would like it to be automated as possible since I'm not the only one using this excel.
 
Upvote 0
Fileformat 51 is an xlsx file and a xlsx file can't hold macro's try

Code:
    savename = Application.GetSaveAsFilename(fileFilter:="Exel Files (*.xlsx), *.xlsx")
    If savename <> "False" Then
        ' save and close new workbook
        With ActiveWorkbook
            savename2 = Mid(savename, 1, InStrRev(savename, ".")) & "xlsm"
            .SaveAs Filename:=savename2, FileFormat:=52   'Something iswrong
        End With
    End If

which should save as a xlsm.
 
Upvote 0
Code:
Sub xlnh()
  Dim sFile         As Variant

  sFile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xlsm), *.xlsm")
  If sFile <> "False" Then
    ActiveWorkbook.SaveCopyAs sFile
    Workbooks.Open sFile
    Application.DisplayAlerts = False
    With ActiveWorkbook
      .Worksheets(Array("Main", "Template")).Delete
      Application.DisplayAlerts = True
      .Save
    End With
    MsgBox "Here's your new file." & vbLf & vbLf & _
           "The original file is still open."
  End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top