none
Pivot table from many sheets

    Question

  • Hello and thanks for reading,

    I have a workbook with 301 worksheets. One sheet per individual, each sheet is updated in rows A-18 to A-41 with software assigned to that employee. I need to create a pivot table or other report that contains the (unique) software and how many installations there are of each.

    It's also important that it be able to be updated as I change the items in the worksheets.

    Please excuse my lack of any technical knowledge in this regard, I am very new to excel and under a crucial deadline. I have searched multiple sites but do not have the VBA knowledge to adapt any code to my situation.

    Thank you for any help that you can provide.

    Monday, April 02, 2012 1:01 PM

Answers

  • Hi Dave,

    I am not sure whether the ranges you selected in each sheet are more than one column or not.

    As a pivot table, the ranges should not be only one column.  

    And the following code can help to dump all of the unique pieces of software into a new sheet. (Suppose the new sheet is “sheet 66”). There are 3 macros.

    1. This sub will copy A18 to A41 of each sheet to sheet66. Including the blank cells and duplicate cells.

    Sub merge_sheets()

    Dim wb As Workbook

    Dim st As Sheets

    Dim sht_num As Integer

    Dim sht_name As String

    Dim cell_num As Integer

    Dim cell_position As String

    Set wb = ActiveWorkbook

    For sht_num = 1 To 301

        cell_num = (sht_num - 1) * 24 + 1

        Worksheets("Sheet" & sht_num).Select

        Range("B18:B41").Select

        Application.CutCopyMode = False

        Selection.copy

        Sheets("Sheet66").Select

        Range("A" & cell_num).Select

        ActiveSheet.Paste

    Next sht_num

    End Sub

         2.  Then choose the range of the content in sheet66. Run the following macro.

    Sub DeleteBlankRows1()

    'Deletes the entire row within the selection if the ENTIRE row contains no data.

    'We use Long in case they have over 32,767 rows selected.

    Dim i As Long    'We turn off calculation and screenupdating to speed up the macro.

        With Application

            .Calculation = xlCalculationManual

            .ScreenUpdating = False   

     'We work backwards because we are deleting rows.

        For i = Selection.Rows.Count To 1 Step -1

            If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

                Selection.Rows(i).EntireRow.Delete

            End If

        Next i

            .Calculation = xlCalculationAutomatic

            .ScreenUpdating = True

        End With

    End Sub

     3. Using the following macro to delete the duplicate rows.

    Sub DeleteDups()  

        Dim x               As Long

        Dim LastRow         As Long

        LastRow = Range("A65536").End(xlUp).Row

        For x = LastRow To 1 Step -1

            If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

                Range("A" & x).EntireRow.Delete

            End If

        Next x    

    End Sub


    Jaynet Zhang

    TechNet Community Support

    • Marked as answer by DSMcL2012 Tuesday, April 10, 2012 1:46 PM
    Tuesday, April 10, 2012 6:57 AM
  • Hi,

    At a guess, the sheet names are the names of individuals, and the software names are in cells A18 to A41.

    In that case, first create a new sheet that will hold the consolidated data. Rename this to "Consol".

    Then select the 301 sheets in question by holding down the shift key and clicking on the first sheet and then the last sheet.

    Then run this bit of code. This will put formulas in the Consol sheet that refer to each of the selected 301 sheets.

    Sub ConsolidateWorksheets() Dim i As Long Dim j As Long Dim sht As Worksheet Dim destSht As Worksheet Dim n As Name Dim DataExists As Boolean ' change this to the destination sheet you want Set destSht = ThisWorkbook.Sheets("Consol") ' data starts in row 18 j = 18 ' code writes formulas in the destination sheet ' to link cells in all the selected sheets With destSht .Cells(17, 1) = "SheetName" .Cells(17, 2) = "Software" For Each sht In ActiveWindow.SelectedSheets ' have data in A18 to A41 = 24 cells, so For i = 0 To 24 .Cells(j + i, 1) = sht.Name .Cells(j + i, 2).FormulaR1C1 = "=" & sht.Name & "!R" & 18 + i & "C1" Next j = j + i Next DataExists = False For Each n In ActiveWorkbook.Names If n.Name = "Data" Then DataExists = True End If Next n If Not DataExists Then ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:="=" & .Name & "!R17C1:R" & j - 1 & "C2" End If ActiveWorkbook.Names("Data").RefersToR1C1 = "=" & .Name & "!R17C1:R" & j - 1 & "C2" End With End Sub

    Now create a pivot table with Table/Range "Data" in the existing worksheet "Consol". Drag "Software" in the Row Labels area, and "SheetName" in the Values area. Filter Row Labels to exclude 0.

    Done. The formulas will update as you add data, you only need to refresh the pivot table.


    Ed Ferrero
    www.edferrero.com

    • Proposed as answer by VBAToolsMVP Tuesday, April 10, 2012 12:39 PM
    • Marked as answer by DSMcL2012 Wednesday, April 11, 2012 1:48 PM
    Tuesday, April 10, 2012 8:20 AM

All replies

  • Hello,

    Are all the sheets in same excel file?

    Monday, April 02, 2012 1:55 PM
  • willsonyano,

    Yes they are all in the same workbook.

    Monday, April 02, 2012 2:53 PM
  • Hi,

    Let’s try following steps:

    1. Click a blank cell in the workbook that is not part of a PivotTable report.
    2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.
    3. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
    4. On the Step 2a page of the wizard, click I will create the page fields, and then click Next.
    5. On the Step 2b page of the wizard, do the following:
      1. For each cell range, click Collapse Dialog to temporarily hide the dialog box, select the cell range on the worksheet, press Expand Dialog , and then click Add.
      2. Under How many page fields do you want?, click the number of page fields that you want to use.
      3. Under What item labels do you want each page field to use to identify the selected data range?, for each page field, select the cell range, and then enter a name for that range.
      4. Click Next.
    6. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

    More information about Consolidate multiple worksheets into one PivotTable report, please refer to the link:
    http://office.microsoft.com/en-us/excel-help/consolidate-multiple-worksheets-into-one-pivottable-report-HA010226585.aspx


    Jaynet Zhang

    TechNet Community Support

    Thursday, April 05, 2012 2:41 AM
  • Jaynet,

    Thank you for your response. I have tried this approach, and unless I am doing it improperly I have two problems:

    One this seems to require selecting the cells I want in the table on every individual worksheet. That means selecting cells in 301 different sheets. Then altering the selections as information is added. Also, when I did try this on just a small set of (50) worksheets, it did provide a list of the items in the cells, but would not sum up the number of each entry.

    The biggest problems that I see is that there will always be some cells in A-18 through A-41 that will be blank. Each user has a different number of pieces of software that they require (that is the values filling these cells), also that there are too many worksheets to make manually selecting the data feasible.

    I was hoping for maybe VBA code that would reiterate through the assigned cells on each worksheet (A-18 - A-41) and dump all of the unique pieces of software into a new sheet. Then, I am guessing, I would need it to rescan the sheets and calculate the number of each individual pieces of software.

    Please let me know if I am missing something or if I am making this more difficult than it needs to be. I have tried a number of solutions with no luck so far.

    Thank you for your help!

    Dave

    Monday, April 09, 2012 12:07 PM
  • Hi Dave,

    I am not sure whether the ranges you selected in each sheet are more than one column or not.

    As a pivot table, the ranges should not be only one column.  

    And the following code can help to dump all of the unique pieces of software into a new sheet. (Suppose the new sheet is “sheet 66”). There are 3 macros.

    1. This sub will copy A18 to A41 of each sheet to sheet66. Including the blank cells and duplicate cells.

    Sub merge_sheets()

    Dim wb As Workbook

    Dim st As Sheets

    Dim sht_num As Integer

    Dim sht_name As String

    Dim cell_num As Integer

    Dim cell_position As String

    Set wb = ActiveWorkbook

    For sht_num = 1 To 301

        cell_num = (sht_num - 1) * 24 + 1

        Worksheets("Sheet" & sht_num).Select

        Range("B18:B41").Select

        Application.CutCopyMode = False

        Selection.copy

        Sheets("Sheet66").Select

        Range("A" & cell_num).Select

        ActiveSheet.Paste

    Next sht_num

    End Sub

         2.  Then choose the range of the content in sheet66. Run the following macro.

    Sub DeleteBlankRows1()

    'Deletes the entire row within the selection if the ENTIRE row contains no data.

    'We use Long in case they have over 32,767 rows selected.

    Dim i As Long    'We turn off calculation and screenupdating to speed up the macro.

        With Application

            .Calculation = xlCalculationManual

            .ScreenUpdating = False   

     'We work backwards because we are deleting rows.

        For i = Selection.Rows.Count To 1 Step -1

            If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

                Selection.Rows(i).EntireRow.Delete

            End If

        Next i

            .Calculation = xlCalculationAutomatic

            .ScreenUpdating = True

        End With

    End Sub

     3. Using the following macro to delete the duplicate rows.

    Sub DeleteDups()  

        Dim x               As Long

        Dim LastRow         As Long

        LastRow = Range("A65536").End(xlUp).Row

        For x = LastRow To 1 Step -1

            If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

                Range("A" & x).EntireRow.Delete

            End If

        Next x    

    End Sub


    Jaynet Zhang

    TechNet Community Support

    • Marked as answer by DSMcL2012 Tuesday, April 10, 2012 1:46 PM
    Tuesday, April 10, 2012 6:57 AM
  • Hi,

    At a guess, the sheet names are the names of individuals, and the software names are in cells A18 to A41.

    In that case, first create a new sheet that will hold the consolidated data. Rename this to "Consol".

    Then select the 301 sheets in question by holding down the shift key and clicking on the first sheet and then the last sheet.

    Then run this bit of code. This will put formulas in the Consol sheet that refer to each of the selected 301 sheets.

    Sub ConsolidateWorksheets() Dim i As Long Dim j As Long Dim sht As Worksheet Dim destSht As Worksheet Dim n As Name Dim DataExists As Boolean ' change this to the destination sheet you want Set destSht = ThisWorkbook.Sheets("Consol") ' data starts in row 18 j = 18 ' code writes formulas in the destination sheet ' to link cells in all the selected sheets With destSht .Cells(17, 1) = "SheetName" .Cells(17, 2) = "Software" For Each sht In ActiveWindow.SelectedSheets ' have data in A18 to A41 = 24 cells, so For i = 0 To 24 .Cells(j + i, 1) = sht.Name .Cells(j + i, 2).FormulaR1C1 = "=" & sht.Name & "!R" & 18 + i & "C1" Next j = j + i Next DataExists = False For Each n In ActiveWorkbook.Names If n.Name = "Data" Then DataExists = True End If Next n If Not DataExists Then ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:="=" & .Name & "!R17C1:R" & j - 1 & "C2" End If ActiveWorkbook.Names("Data").RefersToR1C1 = "=" & .Name & "!R17C1:R" & j - 1 & "C2" End With End Sub

    Now create a pivot table with Table/Range "Data" in the existing worksheet "Consol". Drag "Software" in the Row Labels area, and "SheetName" in the Values area. Filter Row Labels to exclude 0.

    Done. The formulas will update as you add data, you only need to refresh the pivot table.


    Ed Ferrero
    www.edferrero.com

    • Proposed as answer by VBAToolsMVP Tuesday, April 10, 2012 12:39 PM
    • Marked as answer by DSMcL2012 Wednesday, April 11, 2012 1:48 PM
    Tuesday, April 10, 2012 8:20 AM
  • Jaynet,

    Thank you for your response! I was able to get it to work using your code. I left off the last piece of code that removed duplicates and created a pivot table from the software list created by the first two pieces of code.

    The only downfall is that I will need to do this exercise whenever I updated information on the worksheets.

    I am happy to have the numbers though, as I can finally show some progress!!

    Thank you very much!!

    Tuesday, April 10, 2012 1:50 PM
  • Ed,

    Thank you very much for your response! When I run the code, in one column I get a list of employee names and in the other column #NAME? . This is repeated for each user sometimes as many as 50 times. Is this a mistake on my part or possible something I miscommunicated about the spreadsheet?

    Unfortunately due to sensative company data I am unable to share the spreadsheet, but would be happy to give a more detailed description of any part of it that might help.

    -- Edit --

    I am not sure what changed, but now when I run it I am getting the headers (on row 17 of the Consol sheet) SheetName and Software

    Under each column the word Consol is repeated 50 times under each column.

    Thanks!

    Dave


    • Edited by DSMcL2012 Tuesday, April 10, 2012 2:00 PM Update
    Tuesday, April 10, 2012 1:53 PM
  • Hi Dave,

    From your description it sounds like, when you ran the macro a second time, you did not first select the employee worksheets. Try running the macro after selecting just two or three of the 301 employee sheets. Then, if you have a #NAME? error in a cell, select that cell and tell us what formula is shown in the formula bar.


    Ed Ferrero
    www.edferrero.com

    Tuesday, April 10, 2012 8:48 PM
  • Ed,

    You are correct, I had forgetten to select the users. A colleague was able to get the code to work by entering two ticks to "escape" quote so they were entered into the formula (I just can't seem to remember where). This worked great. Thank you very much for all of your help!!

    Dave

    Wednesday, April 11, 2012 1:48 PM