Calculating total using vba

**shotta** · 09-12-2013, 10:04 PM

I would like the attached spreadsheet to calculate the total for all 10 colums on each dated spreadsheet(created daily), and place the results on a sheet named "TOTAL". The problem i have is that each day my code creates sheet in "yyymmmdd" format, so it hides the previous days spreadsheet and creates a new dated spreadsheet each day. Each spreadsheet is copied from a sheet i named "TEMPLATE". what I would like the vba to do is capture the results from the previous days including present day and sum everything up on the "TOTAL" sheet Can anyone HELP with this it is very important and WORK CRITICAL.I am attaching my work so far with vba code

P S the previous days are hidden along with the template. you can unhide it to view

MUCHOS GRACIAS

**ExcelDominator** · 09-13-2013, 07:25 AM

Just to ask, wouldn't it be easier to just append each day below previous day? In that way you would have everything in one sheet and just could summarize data with pivot ... that seems much easier to me. Also, there is a limit on 256 sheets and year has 354 days, so after 256 sheets you could not do it in this way any more ... but if you would just append new data below old data then you would have 100 rows per day * 356 days = approx. 35600 rows/year (based on size of your sheet) what would be still smalled excel file than this.

**6StringJazzer** · 09-13-2013, 09:19 AM

I agree that putting this data on a single sheet and adding a column for date is a much better design than putting each day on a new worksheet. It provides much more power in terms of being able to analyze and report on the data. That would also allow you to do everything with formulas without VBA, so that the data on Totals would be automatically up to date with no user action.

I have done a redesign showing how this can be done. I have added a pivot table on TOTALS to show how you can also report totals by day automatically. File is attached.

However, ExcelDominator, please note there is no limit on the number of worksheets, except for available memory. (The link is for 2003 but also true for subsequent versions.)

**ExcelDominator** · 09-14-2013, 04:12 AM

I had situation where on domain network with terminal users (thin clients) I made application for 40 people (their idea) in which they opened new sheet entered some informations and CEO would just open his excel, click one button and gather all data in one file.
Even though I know that there should not be limits, I ecountered on few computers on 256 sheets limit. Why? I do not know but it was like that even though we all had same thin clients in whole company. But surely, whatever the cause is, 200 sheets in one excel is not an answer.