Solved

Summary of sheets in another sheet in Excel

Posted on 2013-10-30
4
446 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

691 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