Creating a new workbook with VBA including VBA

Eindhoven_Excel

New Member
Joined
Feb 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am using at the moment a VBA code to create a new workbook. It´s financial data and therefore I want to use the group function to show detail in my totals. Next, i want to protect the two newly created sheets and save it as a new workbook. So everything works fine till this point, but after opening the saved file the group function is not available as it is a protected sheet.

Is there a way to create a VBA code that generates a new workbook from a masterfile and saves the workbook as a protected file where you can still use the group/ungroup function? The problem is now that te current code only works till you open a file.

I was thinking of a VBA code that runs everytime you open the saved workbook, but how can you type this in another vba code? So how can you generate a VBA code for a new VBA enabled file.

VBA Code:
Sub VestigingOpslaan()
Dim FileName As String
Dim Path As String
    
    Application.Calculation = xlCalculationManual
    With ThisWorkbook
        .Worksheets("XXX").Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
        .Worksheets("XXXX").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
        Application.DisplayAlerts = False
        ActiveSheet.Protect Password:="XXX", UserInterfaceOnly:=True
        ActiveSheet.EnableOutlining = True

Path = "H:\Documenten\"
FileName = Range("E2").Value & " " & Range("P6").Value & ".xlsm"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True

    End With
    Application.CutCopyMode = False
    Application.Calculation = xlCalculationAutomatic
  End Sub
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,963
You can save a copy of the existing workbook with a new name - less any unwanted data.

You can create a TEMPLATE of the existing workbook with the VBA code included but no data. Then each time save a copy
of the TEMPLATE with a new name.
 

Eindhoven_Excel

New Member
Joined
Feb 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
You can save a copy of the existing workbook with a new name - less any unwanted data.

You can create a TEMPLATE of the existing workbook with the VBA code included but no data. Then each time save a copy
of the TEMPLATE with a new name.
Thanks, didn't think of that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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
Top