Run Sales Reports from Multiple Sheets within a ever expanding workbook

[hayden1616]

Hello, I would still consider myself a newbie when it comes to utilizing the potential excel has to offer. With that being said, I need to extract data to run sales reports from multiple tabs within a workbook. Every tab is labeled a month of the year.
Within each tab there are 8 columns which remain the same. The rows are actually repeat and newly captured clients and very by number each month. I need to figure out a way to be able to continually add new months (tabs) of sales data while continually being able to run the reports. I really would like to utilize the pivot tables if possible. Can anyone assist me in how to merge the data? Thank you!

[Mysore]

Goodday Mate

Can you please post a sample work book and possibly highlight the solution you are after>
Thanks

Mysore

[hayden1616]

I want to run a report to find out how much commission was paid our per client. Some clients will appear in multiple months (tabs/worksheets). I would also like to know how much commission has been paid with each company. These are only two examples of what I would like to do with this spreadsheet. I continually add months as they close so the formula or calculation must account for adding new months (tabs/worksheets). I would like to be able to specify which months I choose to include in my reports based on quarterly, same month but different year, one full calendar year, etc.

Worksheet.jpg

[Mysore]

Hi Hayden

Without having a sample workbook its bit hard to visualise your requirement. Anyway as a start I can usher you with the following tips to get started,

1) In Order to generate a report, its easier as a prerequisite if we have consolidated data from all the sheets into one sheet. This can be achieved in multiple ways depending on what approach you want to take . With Excel 2010/2013 Version, you can adopt Power Query tool that helps to consolidate all your data into one query. A second approach might be to use VBA macro to consolidate the data . The following macro code allows you to consolidate all the data from multiple sheets into a Single Sheet. Please note this macro will work as long as the data structure in all the sheets are similar and your data contents must begin with headers from Row1. You can copy the code and paste it on a module .


2) Once you have the consolidated data, then create a pivot table from this data source.


Hope this helps!

Cheers

Sub CombineSheets()

    'This macro will copy all rows from the first sheet
    '(including headers)
    'and on the next sheets will copy only the data
    '(starting on row 2)

    Dim i As Integer
    Dim j As Long
    Dim SheetCnt As Integer
    Dim lstRow1 As Long
    Dim lstRow2 As Long
    Dim lstCol As Integer
    Dim ws1 As Worksheet

    With Application
        .DisplayAlerts = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    On Error Resume Next

    'Delete the CombinedData Sheet on the document (in case it exists)
    Sheets("CombinedData").Delete
    'Count the number of sheets on the Workbook
    SheetCnt = Worksheets.Count

    'Add the CombinedData Sheet
    Sheets.Add after:=Worksheets(SheetCnt)
    ActiveSheet.Name = "CombinedData"
    Set ws1 = Sheets("CombinedData")
    lstRow2 = 1
    'Define the row where to start copying
    '(first sheet will be row 1 to include headers)
    j = 1

    'Combine the sheets
    For i = 1 To SheetCnt
        Worksheets(i).Select

        'check what is the last column with data
        lstCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

        'check what is the last row with data
        lstRow1 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

        'Define the range to copy
        Range("A" & j, Cells(lstRow1, lstCol)).Select

        'Copy the data
        Selection.Copy
        ws1.Range("A" & lstRow2).PasteSpecial
        Application.CutCopyMode = False

        'Define the new last row on the Target sheet
        lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1

        'Define the row where to start copying
        '(2nd sheet onwards will be row 2 to only get data)
        j = 2
    Next

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Sheets("CombinedData").Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select

End Sub

[hayden1616]

Thank you Mysore your code worked!