Forum Discussion
Creating Duplicate pages in an excel workbook
I have created a 7 page Excel Workbook for tracking daily/weekly/monthly hours for 45 employees on 45 different jobs. One sheet is the bank for the drop-down menus, one sheet is the monthly, and then I have 5 weekly sheets that will need to have approximately 45 Week 1 sheets etc. I have programmed numerous formulas to auto-populate repetitive information. The issue that I am having is that regardless of the paste version I use, I cannot create additional copies of each "job" book without having to reprogram a significant amount of it. I tried to use Macros but it would only run on the original page, not on any subsequent pages. This project is going to take forever to create if I can't figure this out. Hoping someone can help! Thank you!
12 Replies
Is it possible for you to post a copy of the workbook? Just make sure it contains no private/personal or confidential information.
And are you open to wholly different ways to approach your situation? The reason I ask: it's generally a mistake to break a workbook into multiple essentially duplicate sheets--for example, one for each week--when tracking such things as hours worked. Excel has very powerful tools for taking a single database, {into which ALL of that data for each week for each person for each job (whatever) gets entered], and then breaking that data apart into summary reports or summary analyses.
I see that happen a lot.....having had a career that included a stint as the director of the HR/Payroll data base for a major corporation, I can assure you that a single database (in Excel terms, a single Excel Table) can serve you far more effectively than 52 different ones, one for each week of the year. It's relatively easy to include a date column in that single Table that can be used as the basis for weekly reports, weekly calculations, etc.
I have attached a fully functional demo with private information edited out. Sadly, I am bound by the laws of powers higher than myself as to the format of these documents. Until I came along, these were done ENTIRELY by hand. I was granted permission to create a digital version that followed the guidelines of the original document. I currently have a functioning version aside from this document but I still find it to be inefficient as we do not always have hours on every jobsite every week ( yet I am still required to state those hours - or lack thereof) until the job is closed.
The formatting works thus: On the cover page: the jobsite, month, W/E and employee name are selected. This information auto-populates to the subsequent 5 potential weeks of each month. Then, on each weekly report, the hours logged on those pages auto-populate to the cover page and are thus tallied and compiled into the weekly/monthly total hours. I then must PRINT off each sheet for each jobsite and get signatures from each employee to verify their hours and then mail these documents into 'corporate'. I am not allowed to submit these electronically and each job and each week must be accounted for regardless of hours worked.
With that information in mind, I am totally open to any formatting changes you might have. Thank you! (Also to recap: The issue I am having is creating duplicate sheets within each week without losing substantial formatting) Each week could see upwards of 45 different jobsites and each one must be accounted separately)
Oh, my! The issue is that it's not mere "formatting" change that I have in mind. By "formatting" I am referring to the nicely laid out form that you ask employees to sign, verifying hours worked. That format is perfectly fine. But it's an output document.
It's an output document that is being used to dictate how you get input. THAT'S the thing I'd change. Excel is really really REALLY good at taking raw data and formatting it for a whole range of outputs. You're being asked to work with an output document as the receptor of the raw data, and are looking for ways to tweak that. But it isn't a good approach to begin with. You've heard the expression "putting lipstick on a pig"?
And, sadly, the history you recount is a big part of why you are where you are. "We've always done it this way [on paper]; it has worked just fine. We hereby give you permission to take this fancy newfangled automated ledger sheet and convert the old paper forms into it, but, please, keep the old process going just as it was. We'll let you use Excel to do the adding and multiplying, but by no means should you monkey with the process that has worked so well with paper and pencil."
You know something? Excel can do more than fool-proof adding, multiplying, dividing and subtracting. But to use it to its fullest extent, and really achieve less error-prone results, you will need to be open to reconsidering how you collect and store the "raw data"......it's not a problem producing the same output, but do you really really REALLY just want to use Excel because the result will be printed by a computer rather than contain hand-written numbers?
I'm going to spend some time to create a little bit of a demonstration of what could be, but not a lot, since it's not clear that your management is open to actually improving the process if it means changing the process.
If it's the construction business that you're in, you might explore how they'd feel if, upon getting a new power gizmo to do a job, the customer allowed them to use it, but without actually plugging it in. If it were a drill, "you can use it because the bits are sharper than our old one, but you need to turn the whole thing by hand 'because that's the way we've always done it.'"
Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future.
