?
Solved

Summary of sheets in another sheet in Excel

Posted on 2013-10-30
4
Medium Priority
?
447 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

800 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