Solved

Summary of sheets in another sheet in Excel

Posted on 2013-10-30
4
433 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:Caracena
  • 2
  • 2
4 Comments
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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:Caracena
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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:Caracena
Comment Utility
Thanks byundt! This is perfect.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now