You are here:

Excel/Macro or code to fill summary worksheet


Hi Tom,
I have a workbook containing 11 worksheets each of which is a transaction register for a checking or credit card account. Each book started with 500 rows and at present the number of rows containing an entry range from 1 in the least used account to 322 in the most active account. I also have a summary sheet with links to the 500 rows in each account worksheet.

I would like to improve upon this with a macro or code that would pull the data from each worksheet as it is entered so that in the future if I go to row 501 in a sheet it will automatically transfer to the summary sheet instead of me having to remember to go to the summary sheet and increase the number of rows linked to the transaction sheet. Also in the case of the account with 1 transaction I would not have 499 empty rows on the summary sheet which I keep filtered out.

Short version, I want my summary sheet to automatically read and display an unlimited number of only the populated rows in my 11 sheets. Also column A of the summary sheet to show the name of the worksheet that the data comes from.

Hope this is clear, and thanks in advance for any suggestions.


You asked for my suggestion - so I will say that I don't think you suggest approach is the best approach given just the information you told me.

Excel does support event macros that react to an entry in a cell - but in your case, you are not just entering one data point in a single cell.  You are entering data across a row for an unknown number of columns.  So it would be a challenge to code the macro to understand when it should act.  You could specify the data be written when an entry is made in column L but then you would have to make sure you followed that convention.  Also, what if you need to correct and entry.  I would suggest you just have the macro reconstruct the summary sheet each time you run the macro.  I shouldn't take very long for it to loop through you data and rewrite the sheet.  Then when you want the summary sheet updated, you just run the macro.

A non-macro approach would be to make all your entries in the summary sheet.  You could then use a pivot table to build the individual sheets or you could use formulas in those sheets to dynamically pick up their data from the summary sheet.  With a pivot table, you could have only the sheet with the pivot table and you could pick from a dropdown which data you want to show (which account).  A lot depends on the purpose of the individual sheets.  

So I will offer those for consideration and I can provide assistance if you need it.  If you want to go with a macro, then you have to describe you layout and so forth. Also, another consideration is that if you want a macro, macros often require modification - so if you are not reasonably competent at programming macros, then having a macro solution may not be ideal.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.