How to keep the format and formulas from a pivot table?

[GaryG9595]

Hello,
hope all is well. Can anyone help out with this?
I have a macro that goes down the pivot table and creates a sheet for each row on the pivot table.( See Pic 1 and Macro Below).
Works perfectly, but only returns the values on each sheet created.

For this example I will get three sheets created. My header row will be created on each sheet, and then the lines associated to the Row Label.
A&M will have header and 6 lines, Ace Manufacturing will have header and 2 lines, Achieve Industries will have header and 78 lines. The formulas from the original data are now converted to values.

Is there a way to either modify the macro or adjust the pivot table to return the formulas when clicking on each Row Label Count?
I'd also like to keep the color formatting of my header row if possible.

Thank You in advance,
Gary

Pic 1.

Macro Used:
Sub CreateSheets()
'
' CreateSheet Macro
'
Dim currRow As Long
For currRow = 1 To 3
Sheets("Data").Range("E" & currRow).ShowDetail = True
Next
End Sub

 

[RoryA]

Not from the pivot table - they are not stored in the cache, only the values are. If the original data is in the workbook, you could simply process that using filters.

 

[SpillerBD]

Using Measures in PowerPivot can give a similar ability. The actual formula would be on the PowerPivot display or Measures list.
Most Pivot Table evaluations are equivalent to a SUMPRODUCT array formula.
While your goal is valid, whatever that is beyond your post, there is likely a better technique to achieve your end result.

 

[GaryG9595]

Thank you both RoryA and Spiller BD. I appreciate it
I am searching for a better technique and have found many solutions here, and hopefully they help others as well.
Currently I have a workbook with a "Master" sheet that pulls in data from multiple sheets, all in one workbook through lookups.
That "Master" sheet contains multiple customers that I use a pivot table to isolate what data I want to export out to the customer.
Then I use that macro above to create the sheets per customer and then another macro to rename those sheets to the customer and split those newly named customer sheets into a folder to be worked individually. This particular request has 88 Customers to single out.
The problem with that is the formulas are turned to values, but in some columns, the calculation ones, I would like them to contain the formulas, so the customer can simply add their info in column B and column C would contain the formulas to add their qty's automatically.

It sounds like that might not be possible.

While I was thinking about solutions for this, I thought of saving the same file targeting a list on a sheet, actually same pivot table.
So A2 would save as A&M,A3 would save the file as Ace, A4 would save the file as Achieve.
I found this code that works, but it contains all the customers data. I now have to go in and open them and filter out the customers, but this at least saved some time.

So this is where I am at currently and now I need to see if I can add to this macro to filter only the items on the master sheet to match the name of the file being saved.

Thanks for your help and hopefully this helps others as well.
I'll update this thread if I find a solution. Please do the same if you run across one as well.
Thanks again,
Gary

Macro to save the file under a different name from a list in Excel.
Option Explicit

Sub saveAs()

Dim nameRng As Range
Dim cell As Range

Set nameRng = Sheets("Pivot").Range("A2:A88")

For Each cell In nameRng
'defaults to location of where this file is stored
ThisWorkbook.saveAs cell.Text, xlOpenXMLWorkbookMacroEnabled
Next cell

 

[SpillerBD]

I haven't quite thought of something exactly on the formula question. Maybe a clearer example of what you have and are trying to achieve. An appropriate bit of code could be called in the For Each ws loop in below macro.

So have you been introduced to Pivot Table feature, "Show Report Filter Pages"? It is under the PivotTable Analyze ribbon in the Options drop-down.
After using that, I would select the created Sheets and do copy paste Values on all the sheets and then with the sheets selected run the following macro to send each sheet to a file in an appropriate folder. File names based on the the Sheet Name.
NOTE. The Filter items become the sheet names as long as the number of characters is below 32. This may require a duplicate field with truncated text.

Sub SplitSelectedWorkSheets()
    Dim ws As Worksheet
    Dim DisplayStatusBar As Boolean
    Dim DestinationPath As Variant
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path & "\"
        .Title = "Select a destination folder or create a new destination."
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            'MsgBox .SelectedItems(1)
            DestinationPath = .SelectedItems(1)
        End If
    End With
    
    DisplayStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = ActiveWindow.SelectedSheets.Count & " Remaining Sheets"
    
    For Each ws In ActiveWindow.SelectedSheets
        Dim NewFileName As String
       
        'Macro-Enabled
        'NewFileName = ThisWorkbook.Path & "\" & ws.Name & ".xlsm"
        'Not Macro-Enabled
            NewFileName = DestinationPath & "\" & ws.Name & ".xlsx"
            ws.Copy
            'ActiveWorkbook.Sheets(1).Name = "Sheet1"
            'ActiveWorkbook.SaveAs Filename:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.SaveAs fileName:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False

    Next
    
    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.DisplayStatusBar = DisplayStatusBar
    Application.ScreenUpdating = True
    Close 'close all files and folders?
End Sub