Copying sub workbook macros across

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
717
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
717
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,403
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.
Code:
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.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   ' Your code here
End Sub
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
717
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.
Code:
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.
Code:
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

Well-known Member
Joined
Feb 2, 2003
Messages
717
Prevent user from saving file with filtered data

m
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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