How to present my data?

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
235
Office Version
  1. 365
Hi.

I have a single, simple Excel sheet but I'm not sure how to present the data. To simplify, the data looks like ...

CostCentre | TransactionDate | UserName | Cost | Cost Centre Owner
abc1 | 01/01/18 | John.Smith| 0.72 | Jim.Jones
abc2 | 02/01/18 | John.Smith | 0.13 | Cat.Smith
abc3 | 02/01/18 | Bella.Williams | 0.26 | Jim.Jones

This month, there's over 27,000 rows in this sheet

What I'd like to do ultimately, is email each cost centre owner (there's about 70 cost centre owners, each owner can have one or more code), telling them who has charged to their cost centre(s) this month.

How can I achieve this?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Add a helper column to duplicate the Cost Center Name. Limit the character length of this field to 12.
Build a Pivot Table.
Drop the Helper column into the FILTER field of the Pivot Table.
In the Rows drop all the non-value labels.
Drop cost into the Values area, and ensure your desired formatting and calculation.

On the Pivot Table Ribbon/ Analyze, click the drop down for Pivot Table. Click the Drop Down for Options, and then click Show Report Filter Pages...
Now, select all those new sheets. Select all Cells, Copy, Paste Special Values. (There are other ways to disconnect the data but this is most assured.)
You can accomplish editing of the header rows and columns for all sheets while they are selected as a group.

Use the following macro to copy all sheets to a desired folder, the new files being named as the SheetName and ready to send.

Code:
'SpltSheets
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
 
Upvote 0
Add a helper column to duplicate the Cost Center Name. Limit the character length of this field to 12.
Build a Pivot Table.
Drop the Helper column into the FILTER field of the Pivot Table.
In the Rows drop all the non-value labels.
Drop cost into the Values area, and ensure your desired formatting and calculation.

On the Pivot Table Ribbon/ Analyze, click the drop down for Pivot Table. Click the Drop Down for Options, and then click Show Report Filter Pages...
Now, select all those new sheets. Select all Cells, Copy, Paste Special Values. (There are other ways to disconnect the data but this is most assured.)
You can accomplish editing of the header rows and columns for all sheets while they are selected as a group.

Use the following macro to copy all sheets to a desired folder, the new files being named as the SheetName and ready to send.

Code:
'SpltSheets
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

Many thanks for the quick and detailed reply. I'm still working on your solution and getting my head around how it works and tweaking it, but just wanted to say a massive thanks and not look like I wasn't grateful for the response.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top