I have a master worksheet that I plug a few variables (Store # and Group). Based on these variables, I have formulas and an embedded macro doing things (the formulas look at different data based on the Group input and the macro hides and unhides rows based on that Group). Everything works exactly as it should...until I try to then copy this report to a new workbook and "value-out" its formulas (meaning I copy everything, then paste special - values so that the data doesn't change and the overall size of the file is reduced). I've done this a million times with other reports, but I've never had an embedded macro in the worksheet.

So I basically want to create a new workbook, without the embedded macro.

Here's the code for the embedded Worksheet Change Macro:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("U14")) Is Nothing Then

With Range("U14")
Application.EnableEvents = False

Select Case Target.Value

    Case "STORES"
        
    Range("18:29").EntireRow.Hidden = False
        
    Case "CORPORATE"
    
    Range("18:29").EntireRow.Hidden = True
    
        
    End Select
    Application.EnableEvents = True
    
    
    End With
    End If
    

Set prange = ActiveWorkbook.Worksheets("REPORT").Range("AL17").Cells

If Not Intersect(Target, Range("C2")) Is Nothing Then

With Range("C2")
Application.EnableEvents = False
    
    Range("33:1032").EntireRow.Hidden = False
        
    Range(prange).EntireRow.Hidden = True
        
    Application.EnableEvents = True
    
    End With
    End If
    
End Sub
and here's my code for copying the worksheet:

Sub CREATE_REPORT()
'
' PY12 Macro
' Macro written 01/27/14 by Matthew Mickle

'
   Application.DisplayAlerts = False
      
    
   'Date
   Set drange = ActiveWorkbook.Worksheets("REPORT").Range("AL3").Cells
    
   'Store Range
   Set srange = ActiveWorkbook.Worksheets("REPORT").Range("AL6").Cells
   
   'Store 3 Digit Format
   Set sname = ActiveWorkbook.Worksheets("REPORT").Range("AN2").Cells
   
   'Password
   Set prange = ActiveWorkbook.Worksheets("REPORT").Range("AT2").Cells
    
   
   On Error Resume Next
    
   'Store Sheets
   
   
   
   Sheets("REPORT").Activate
   Range("U14:Z15").Select
   ActiveCell.FormulaR1C1 = "STORES"
   
   For Each aname In ActiveWorkbook.Worksheets("REPORT").Range(srange).Cells
   
   Sheets("REPORT").Activate
   Range("C2:D2").Select
   ActiveCell.FormulaR1C1 = aname
    
   Sheets("REPORT").Select
   Sheets("REPORT").Copy
   Sheets("REPORT").Select
   Sheets("REPORT").Activate
            
   Cells.Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
    
   
   
   With NewWkbk
        
        ChDir "\\golub.com\depts\HR-Share\PY12\STORES"
        ActiveWorkbook.SaveAs Filename:="\\golub.com\depts\HR-Share\PY12\STORES\" & sname & "-PY12_" & drange & ".xls", FileFormat:= _
        xlExcel8, Password:=prange, WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
      
   ActiveWindow.Close
   Windows("py12_MASTER.xlsm").Activate
   ActiveWindow.WindowState = xlNormal
    
   Sheets("REPORT").Select
   
    
End With
    
Next
    
    'Corporate Sheets
            
    
    
    Sheets("REPORT").Activate
    Range("U14:Z15").Select
    ActiveCell.FormulaR1C1 = "CORPORATE"

    For Each aname In ActiveWorkbook.Worksheets("REPORT").Range(srange).Cells
    
    Sheets("REPORT").Activate
    Range("C2:D2").Select
    ActiveCell.FormulaR1C1 = aname
        
 
    Sheets("REPORT").Select
    Sheets("REPORT").Copy
    Sheets("REPORT").Select
    Sheets("REPORT").Activate
            
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    
    With NewWkbk
        
        
        
        ChDir "\\golub.com\depts\HR-Share\PY12\STORES"
        ActiveWorkbook.SaveAs Filename:="\\golub.com\depts\HR-Share\PY12\STORES\" & sname & "-PY12_" & drange & ".xls", FileFormat:= _
        xlExcel8, Password:=prange, WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
      
    ActiveWindow.Close
    Windows("py12_MASTER.xlsm").Activate
    ActiveWindow.WindowState = xlNormal
    
    Sheets("REPORT").Select
    
End With
    
Next
    
   
Call YesNoMessageBox2

End Sub