Copying sub workbook macros across

[Corleone]

I have a macro in my spreadhseet which selects certain tabs from the master file and creates a separate copy of the spreadhseet containing just these selected tabs.

within the master file i have a private sub workbook macro.

the issue i have is when i create my copy of the master spreadsheet, it copies all the worksheet macros but not the workbook macro

is there any way around this?

 

[Corleone]

I have a macro in my spreadhseet which selects certain tabs from the master file and creates a separate copy of the spreadhseet containing just these selected tabs.

within the master file i have a private sub workbook macro.

the issue i have is when i create my copy of the master spreadsheet, it copies all the worksheet macros but not the workbook macro

is there any way around this?

Alternativly, is their a way of creating a worksheet event code which is activiated when the file is saved?

 

[AlphaFrog]

You could make a macro that saves your current workbook with a new name and deletes the sheets you don't want. This way any workbook macros are kept.

Sub MySaveAs()
    
    Application.DisplayAlerts = False
        Sheets("Sheet2").Delete
        Sheets("Sheet4").Delete
    Application.DisplayAlerts = True
    ThisWorkbook.SaveAs Filename:="MyFile"
    
End Sub

There is also the Workbook_BeforeSave event macro. This is located in the ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   ' Your code here
End Sub

 

[Corleone]

You could make a macro that saves your current workbook with a new name and deletes the sheets you don't want. This way any workbook macros are kept.

Sub MySaveAs()
    
    Application.DisplayAlerts = False
        Sheets("Sheet2").Delete
        Sheets("Sheet4").Delete
    Application.DisplayAlerts = True
    ThisWorkbook.SaveAs Filename:="MyFile"
    
End Sub

There is also the Workbook_BeforeSave event macro. This is located in the ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   ' Your code here
End Sub

Many thanks for this - i will have a look at it in the morning

 

[Corleone]

Prevent user from saving file with filtered data

m