[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Summary of sheets in another sheet in Excel

Posted on 2013-10-30
Medium Priority
448 Views
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
``````

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?

0
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
• 2
• 2

LVL 81

Expert Comment

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

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

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
``````
0

LVL 6

Author Closing Comment

ID: 39613892
Thanks byundt! This is perfect.
0

## Featured Post

Question has a verified solution.

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

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…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month13 days, 22 hours left to enroll