I'm asking here.............because you guys are the only help I know...... Say that I have a daily report set up in Excel to record the movement of material that my company handles. It's an easy report for workers to fill out in that they simply open the master file, click on a button, and a macro inserts a new sheet, names it with that day's date, and provides cells for the worker to record the various types of tons moved (by rail, barge, etc.), and then there is one cell that shows the total of all tons moved. I'd like to create a "month to date" totals sheet that adds the totals from the various daily sheets. How can I do that? (I'm an Excel dummy). It seems to me that the biggest problem is creating a formula that references sheets that aren't there yet. For instance, after the second day of the month, there are only two daily sheets created, and I need to add the total of those two days. But on the third day (and so on), I need the formula to be smart enough to know that another day (sheet) has been added. Does my question make sense?
Haven't tested it, but I have a theory. Create an End-of-the-Month tab that looks just like the others, but has zeroes in all the cells where people ordinarily enter data. Then (either on another tab or lower on this same tab), enter a sum formula for each line that looks like this: =SUM(Sheet1!:Sheet32!A1) (With Sheet1 being the first day of the month and Sheet32 being your EOM tab I told you to make). When you do a sum in that format, it will sum the A1 cells from the first tab and last and all the tabs in between. But, the order is important; you have to make sure that everything you want summed falls between those two tabs. You can adjust your macro so that it adds the new tab right before the end tab. Does that make sense?
I don't remember how they work, but "vblookup" may be what you're looking for. You can look it up in Excel's help.
Needless to say....I ran into a snag. Mainly because I'm the aforementioned Excel Dummy. I have a feeling that this indicates that I'm also a VD dummy (which I already knew). I'm assuming that this is the part of the macro that I need to "adjust": Sheets("Blank Report").Select Sheets("Blank Report").Copy After:=Sheets(ActiveWorkbook.Sheets.Count) How exactly do I go about doing this?
Pole, my VB is pretty mediocre (at best). But, I used the Record a Macro... function for copying a sheet to see what the VB looks like. Looking at that (and not knowing the particulars of the rest of your macro), I think a very simple change might do it: change "After" to "Before." What it does after the := is it counts the number of sheets to the right you want to go. So, when I did mine, it said Sheets(13), or move 13 sheets over. But, your code has it copy after that sheet to put it at the end. You now want it second from the end, so you should say Before. Edit: part of that was unclear. It isn't that it moves the copy a certain number of sheets over, but moves it to a slot that is counted from the far left. So, moving a sheet from the 15th slot to the 14th would say "Move Before:=Sheets(14)".
Darn....it all "looks" like it should work now.....but it doesn't. The macro adds the new sheet in the right place. The formula doesn't complain. It all looks pretty. It just won't add the cells unless they exist at the time of the formula creation. Oh well.....Thanks a million for the help Juan (I at least learned a few new tricks). It may just not have meant to be.
Lol, that's a good question. Why can't you make the sheets in advance and they just pick the one with the right date? That'd make summing a lot easier. Not sure why I didn't think to ask. I ran an experiment to see if I can make my own idea work -- I guess I could have tested it before suggesting it -- and it seems to work fine. I can move things in, move them out, even copy tabs or add new sheets, and the formula will accomodate. The difference I can think of is that I haven't tried using a macro to do these things. Perhaps Excel goofs when the macro is run? I'm not ready to give up. You're giving up?
Part of me wants to give up. It wasn't my project to begin with, but someone asked me for help because I'm in IT. I guess its the same thing when they ask me for help with the coffee maker or the TV remote. If it's more complicated than opening a jar of peanut butter--get someone from IT. Anyway, I don't know why they have it set up this way, but I doubt they'll want to change it. The people out in the field who fill the sheets out are not the most computer illiterate bunch, so changing things that they're accustomed to should only be done when absolutely necessary. As for the macro, the only thing it seems to be doing is copying the blank report and renaming that sheet to today's date.
Is Excel set to calculate automatically or manually? To be sure, hit F9 to force a calculation and see if the formula includes your newly added sheet. Otherwise, I don't know what you can do short of changing the architecture of the spreadsheet a bit. Would it kill anyone to have to click on today's date instead of clicking the macro button?
Interesting discovery: I kind of modified Juan's approach (from the start--I'm just now mentioning it). The basic XLS file has a "menu" which is the first sheet. On the menu is where the field person finds the "button" to create the day's sheet. The next sheet is the "blank report" and this is the report that is copied for each day's new sheet. When the macro copies the blank report, it renames that sheet with the day's date. I created a "MTD Totals" sheet, and positioned it at the end. Under "view code" when right clicking the name of a sheet, I renamed the (Name) of my MTD Totals to Sheet34 whereas the menu and blank report sheets are named Sheet1 and Sheet2, respectively. I don't know enough about Excel to speak intelligently about this, but I know that each sheet has a Name (what is displayed on the tab) and a (Name) {which I assume is what Excel uses for something}. Anyway, if I change the Name {not the (Name)} of the blank report tab, it updates my formula for all inserted sheets to where the formula works (just for those added sheets though). I can change the name back to the original "blank report" and the formula still works (for sheets that are already inserted). Once I insert a new sheet though (by using the macro that creates the daily report), those sheets added after the name change are not added into the formula's calculations. I don't know if I'm making any sense, but what I'm seeing is that the formula is not automatically updating itself for the additions of the new sheets. It will only update itself when I make a change to the Name of the first sheet in the formula--and the update is only good for those sheets that have already been added. Furthermore (another thing I hadn't mentioned); I never actually got the formula to work using the (Name) of the sheets {Sheet1, Sheet2, etc.}. I could only use the Name (no Parenthesis) of the sheets in the formula--in this case, =SUM('blank report:MTD Totals'!G40)
Why is that you're messing with the (Name) in the first place? I guess I don't understand what you're saying. Anyway, if the formulas do update when you change the tab name and then change it back, why don't you make that part of the macro?
As for the (Name), I thought that might have been what you meant--the reason being (and of course, you didn't know this) was that my sheets weren't Named Sheet1, Sheet2, etc..... As for changing the macro....that makes sense. I'll see if I can get that to work (I'm kind of fumbling around with the macros because I never work with them, but I assume that I can record a macro, then copy the "meat" of it into the first macro?)
Pole -- Having realized that you've already stated changing what you are already doing is not an option, I still have to ask -- why isn't this being done in Access or another database program? It would seem to me that if you are recording this data for tracking purposes, storing it in a database would be the most logical option. Then you could look at the data any you wanted to (sum by month, by year, by material, etc). Such a database would be fairly easy to set up and with the form-building function already in Access, you could create a form that mirrors almost exactly what the workers are doing now. As is, you can write code that will count the instances of the daily sheet, then loop through the collection and sum the cells you need to sum. I'm at work so I can't do much now, but you still need help tonight, I'd be more than happy to assist
I REALLY appreciate the offer....and I KNOW that our business processes could use some help in many areas. We just got bought out by a company that actually has some developers on board (I'm strictly networking), but they've got their hands full for years to come, and for that matter, they're "supposedly" working on integrating some of the info we were discussing earlier into some of the software that they've built. Still, it could be months and months before that is ready (They told us 45 days last December--as far as I know, we're still "45 days" out). Anyway, the point is: I know that our company is WAY to dependent on excel. We have Excel files for capital aquisitions that are over 10 MB! This particular thing serves a small purpose, and this is a nice quick bandaid until some developer takes over and saves us from ourselves. And JuanV--THANK YOU THANK YOU THANK YOU!. It works, I made someone happy, and now I can get back to doing what I do best--building my post count.
