Save and preserve VBA code on the new workbook

[excelnoobhere]

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.

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

 

[shg]

You could instead do SaveCopyAs, open the new workbook, and then delete sheets main and template.

 

[excelnoobhere]

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.

 

[MARK858]

Fileformat 51 is an xlsx file and a xlsx file can't hold macro's try

    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.

 

[shg]

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

 

[excelnoobhere]

this is so much simpler and easier, it worked perfectly. thank you

 

[shg]

You're welcome.