Solved

Summary of sheets in another sheet in Excel

Posted on 2013-10-30
4
445 Views
Last Modified: 2013-10-31
Hi guys.

I have an Excel workbook with 4 sheets where I have 4 different list of inventory from 4 different work teams. All 4 sheets are identical except for their content which varies from one work team to another.

I would like to add a 5th sheet that includes all the contents from the inventory sheets (1 to 4). It would have the same format (same columns) but of course the number of rows will be the same as the sum of the rows from the first 4 sheets.


I found the following macro on the internet:

Sub MakeSummary()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'

'
    Sheets("SUMMARY").Select
'   Clear the existing values (if any)
    Range("$A$2:$D$60").Value = ""
'   J tracks the row number on the summary page
'   I tracks the sheet number being processed
    J = 2
    For I = 2 To Sheets.Count
        A$ = Sheets(I).Name
'   Don't process a sheet if its name is "Conversion Table"
'   or if the name is blank.
        If (A$ = "Conversion Table") Then GoTo 10
        If (Sheets(A$).Range("$C$1").Value = "") Then GoTo 10
'   Process the current sheet
        Range("A" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C3"
        Range("B" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R2C3"
        Range("C" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R37C8"
        Range("D" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R38C8"
        J = J + 1
10    Next I
End Sub

Open in new window


I quite understand how this works but I have two problems.

My first problem is that inventory (items) are inside tables in the first 4 sheets. These tables are named so it would be easy to call them in the latest part of the macro. Just don't know how to do it.

The second problem is that the new (5th) table also have a table that needs to be filled but using this macro fills the first row of my table and the rest of the rows land outside (below) my table. My table is automatic so when you start typing in the next row, the table gets extended one row down... it doesn't happen with this macro.

Any clues?

Thanks in advance!
0
Comment
Question by:Cesar Aracena
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39612517
Could you please post your workbook? You can use fake data (and don't need much of it), but the table names and column names need to be real.
0
 
LVL 6

Author Comment

by:Cesar Aracena
ID: 39612947
Sorry for the delay. Here it is.

If you notice, there is only one line per sheet so in the 5th sheet, there should be 4 lines at the end.

Please feel free to delete the macro (module) included. It is something like the one I posted above.
DAQ.xlsm
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39613035
Here is a macro that will summarize your data:
Sub MakeSummary()
Dim i As Long
Dim lst As ListObject, lstTotal As ListObject
Dim ws As Worksheet, wsTotal As Worksheet
Dim cel As Range, rg As Range, rgData As Range
Application.ScreenUpdating = False
Set wsTotal = Worksheets("INVENTARIO TOTAL")
Set lstTotal = wsTotal.ListObjects(1)
For Each ws In Worksheets
    If Not ws Is wsTotal Then
        Set lst = Nothing
        On Error Resume Next
        Set lst = ws.ListObjects(1)
        On Error GoTo 0
        If Not lst Is Nothing Then
            Set rg = lstTotal.DataBodyRange
            Set cel = rg.Cells(1, 1)
            For i = rg.Rows.Count + 1 To 1 Step -1
                If Application.CountA(rg.Rows(i)) > 0 Then
                    Set cel = rg.Cells(i + 1, 1)
                    Exit For
                End If
            Next
            Set rgData = lst.DataBodyRange
            If Application.CountA(rgData) > 0 Then cel.Resize(rgData.Rows.Count, rgData.Columns.Count).Value = rgData.Value
        End If
    End If
Next
End Sub

Open in new window

0
 
LVL 6

Author Closing Comment

by:Cesar Aracena
ID: 39613892
Thanks byundt! This is perfect.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question