Split XLSM-file into pieces and keep source model

[Gerbie1]

Hi!

I have a challenge... I have a masterfile with sales-information in a database and nicely layout in pivot tables. I created a macro to split this information by account so its shows the pivot table with the sales data. My issue is - on these nicely layout pivottable sheets, I have some macro-buttons to "show" and "hide" detail, "reset" filters etc...

When I run my macro the new file is saved as an XLSX and the macro-buttons will not work in the individual file. So my question is - how can I make sure that the "Module" is also being taken over into the newly create file so that the buttons still work fine? Secondly, how do I get it saved as XLSM? (so that the macro works).

Sub Run_StratAccountRep()'
 SAD = ActiveWorkbook.Name
 Sheets("vlookup").Select
 a = 1
 SA = Cells(a + 1, 4)
 
MMM = Cells(2, 2)


If MsgBox("Are you sure you want to continue, all previous reports (with same name) will be overwritten", vbOKCancel) = vbCancel Then
Exit Sub
Else
End If




If Dir("C:\Strat Account\") = "" Then
    MkDir Path:="C:\Strat Account\"


End If


Do While Not SA = "END"


     Windows(SAD).Activate
     
      SA = Cells(a + 1, 4)
              
             b = b + 1
        
            
           Sheets("db1").Select
           Selection.AutoFilter
           Range("B11").Select
           ActiveSheet.Range("$A$4:$Q200000").AutoFilter Field:=8, Criteria1:= _
           SA
           Cells.Select
           Selection.Copy
           Sheets("DBpivot").Select
           Range("a1").Select
           ActiveSheet.Paste
           Sheets("1- Strat Acc").Select
           ActiveWorkbook.RefreshAll


        Windows(SAD).Activate
        Sheets(Array("1- Strat Acc", "2 - Strat Acc by Rgn-Cntry", "3 - End User Report") _
        ).Select
        Sheets(Array("1- Strat Acc", "2 - Strat Acc by Rgn-Cntry", "3 - End User Report") _
        ).Copy
    
    
    ' Sheets("1- Strat Acc").Activate
     '   Sheets("pivot").Copy
        
        SAR = ActiveWorkbook.Name


        Range("a1").Select


     Application.DisplayAlerts = False


     ActiveWorkbook.SaveAs Filename:="c:\STRAT Account\" & SA & MMM
     Application.DisplayAlerts = True
     ActiveWorkbook.Close
     Sheets("vlookup").Select
     a = a + 1
     SA = Cells(a + 1, 4)
Loop


Application.ScreenUpdating = False
End Sub

Thanks for your views!!

 

[Derek Brown]

Hello and welcome to The Board.
Could it be the following?

ActiveWorkbook.SaveAs Filename:="c:\STRAT Account\" & SA & MMM & ".xlsm"

 

[Gerbie1]

Thanks for your reply! Yes - tried that...
The issue is that this will not bring over the VBA/Module1 from my Masterfile to my newly created file.... so I guess I need a code/way to ensure that the Marco functions of Module1 are also copied into the new file...

Any solutions for that?

thanks!

 

[Derek Brown]

Have a look at the following:
Import and Export VBA code
Copy modules from one workbook to another using VBA in Microsoft Excel | Microsoft Excel Tips from Excel Tip .com / Excel Tutorial / Free Excel Help