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.
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