Spreadsheet Optimization

[DAULT]

I am trying to optimize the spreadsheet files we use for our timesheets and for tracking hours booked to a project. Currently we have a timesheet file that has the following functions:

1. Entering a project number in a specific cell will infill the sheet header with project information from a separate file which is a job list containing the information.
2. Once the project number is entered, the body of the file contains two years of dates and formulas that use the project number to search each employees separate timesheet files and inputs the hours booked to the project in the appropriate date range for that employee.
3. The file uses billing rate values in the sheet and more formulas to multiply the number of hours booked by each employee against their billing rate and come up with a total dollar value billed to the project for each two week period.
4. The sheet tracks the ongoing cumulative total billed to the project as well as expenses and invoices that are manually entered.
5. In order for the ledger file to work, the timesheet files need to be created ahead of time for the coming year.

We have had a few issues with the files as follows:

1. The file contains merged cells that are used to display the hours booked to a project and for some reason the formulas work for the first 10 or so rows, but then they display a value error.
2. They do not offer much in the way of flexibility in terms of being able to add or remove employees.
3. They require a significant amount of work to update each year to link to new timesheet files for each year.

This is what I am hoping to accomplish.
1. Come up with a better way of importing data from timesheet files that would eliminate the hundreds of formulas in the cells that search the separate timesheet files. Ideally this would be done using a macro.
2. Come up with a way to have the date range start on the date the project is started (this information is imported from the job list file using a formula) and end two years later, regardless of when the project is started.
3. Improve the flexibility of adding and deleting staff members and automatically linking their associated timesheets.

What I am thinking is there must be a way to create a macro that would search the timesheet associated with the name the column and the date range in the row and imput the hours in the corresponding cell based on the project number that is entered in the sheet.

HELP!!!

[danielexcelvba]

If you would like help creating a macro to do something like this you'll need to upload a sample workbook showing representative data and the desired end result (you can de-sensitize the data) so that I or someone else can get a decent idea of how to approach this. Also, a moderator may want to move this thread to the VBA/macro forum but we'll see.

[DAULT]

Thank you. I have attached a sample file of a typical timesheet as well as the ledger. I am not able to share the job list, however it ultimately won't affect what is producted I don't think.

Cell B6/7 is where the job number is manually entered into the job cost ledger.
Cells B9 through B112 is where I want to the dates to be displayed. I would need to have the dates start on a pay period, which are the dates currently represented on the sheet. We could easily make the 'start date' of the project be the first day of the previous pay period to simplify things.
Cells C9 through R112 are where we would want the information found in the timesheets to be displayed.
Cells C5 through R5 are the billing rates
Cells C6/7 through R6/7 are the employee names

Thank you in advance for your help.

[danielexcelvba]

Alright here's what I came up with for a test run. This searches all the sheets in the Timesheet doc that start with "Per", and inputs the dates/data into columns "B" and "C" of the master sheet (assuming this would be for the "Robot" time sheet). Is that what you had in mind?
Also, for the final product, do you have a folder full of time sheets that you want to search through to populate the master sheet, or will they all be opened when you run the macro?
Finally, how would you like the macro to be run. Your options would be manually (going to macros > run, VBA window > run etc.), having a button on the master sheet, or having it run every time you enter a value in cell "B6" of the master sheet.
Here's the code and the edited docs.

Please Login or Register  to view this content.

[DAULT]

Thank you for the response. To answer your questions, the time sheets are all located in the same directory and would not be open when the ledger file is updated, so ideally the ledger would update automatically based on the last saved content of the time sheet files. If you tell be where in the code the directory link needs to be placed, then I can add that. With respect to the job cost ledger itself, there is a single file for each project number, therefore the code would need to be generic so that the filename can be anything as opposed to being called by the specific filename. Make sense?

Will this work for multiple timesheets with different names or would these need to be code added for each timesheet file?

Did you modify the timesheet file itself other than adding in time values? The only reason I ask is because I don't want to over right my file here as they are protected and a lot of work has gone in to making them work.

[DAULT]

Ideally in addition to the macro running on start-up of the file, there would also be a button to update while its open.

[DAULT]

I opened the file and tested the macro and it got an error on line 3 where it attempts to clear the data.

[danielexcelvba]

Do you mean to say there is one timesheet per employee name? Because it appears as though each timesheet has the information for multiple project numbers.

This could work for multiple timesheets with some editing, but I would need to understand which data needs to be pulled from which timesheet.

I only added the values to the timesheet. However, I wouldn't test anything on your actual data unless you have a backup copy as the results are permanent

[danielexcelvba]

I opened the file and tested the macro and it got an error on line 3 where it attempts to clear the data.

Did you test it on your real workbook? If so, it is likely because the sheet and/or workbook names are different. Or did you only have one of the two workbooks open? As of now, this code requires both of them to be open as I have not added any sort of directory.

[DAULT]

Both of the files you sent to me and both of the files open.

[danielexcelvba]

That's strange it worked for me. What was the error message?

[DAULT]

Run-time error '9': Subscript out of range

[danielexcelvba]

Run-time error '9': Subscript out of range

Most likely is a name issue for the workbook/sheet like I said. My guess would be you are running the macro on a book called "2019 Job Cost Ledger SAMPLE (1)", as downloading a book with the same name as an existing book will add a subscript (i.e. "(1)"). You would have to save it as the original workbook name after downloading, or edit the code to include the subscript.

[Mumps1]

Place the first macro in a regular module in the "Job Cost Ledger Master" workbook. Change the folder path where indicated in both macros to the folder that contains the source files. The second macro will rename the sheets in the source files using the start date in "Sheet Summary". Run the second macro before you run the first one.

Please Login or Register  to view this content.

[DAULT]

Thank you. Troubleshooting one thing at a time, I did the following for the tab renaming.

I opened by timesheet and added the macro as a button in my timesheet.xlam file. When I try to use the macro, I get the following error.

Cannot run the macro 'Timesheet.xlam!ThisWorkbook.ReNameSheets'. The macro may not be available in this workbook or all macros may be disabled.

Sorry for the basic questions - its been a while since I worked with macros...

[Mumps1]

The "ReNameSheets" macro cannot be placed in any of the time sheets. It must go in a different workbook and run from there. You could put it in the "Job Cost Ledger Master" workbook or even in a blank workbook.

[DAULT]

Ok, I moved it to a button in our job list, but it doesn't work and does not return an error.

[DAULT]

Also, when I add the first macro to the job cost ledger file I get a 400 error. That's all it says, no message.

[DAULT]

Since the first macro wasn't working, I manually changed the tab names in one timesheet. Will that make a difference? What about the date format? Does this macro also pull the names from the file to add in the names section of the job cost ledger?

[Mumps1]

In my testing the macros worked properly. I've attached the four files that I used. Create a folder named "Test3" in your C:\ Drive. Place the four attached files in that folder. Open the Job Cost Ledger and run the "ReNameSheets" first. Next run the "CopyHours" macro. Have a look at the results.

[DAULT]

Ok, so your files work. I will double check what I have set up in my files and find what I did wrong. While doing that, I have another challenge for you. A lot of our projects are going to have time charged over multiple years. Is there a way to search subfolders for year named timesheets and to pull in all information found related to the project number regardless of the year? So, in your case you would have Test3/Timesheets and within that folder you would have a timesheet folder for 2018, 2019 and so-on. Each of the employees with timesheets in any of those folders with hours charged to the project would need to be included in the ledger.

[DAULT]

After thinking about it...maybe they don't need to be in a subfolder...

[DAULT]

Ok, I think we can live with the timesheet files all being in one folder, regardless of the year, so no need to search subfolders. The issues seems to be the path. Our path is Z:\Current\_Timesheets\Timesheets. When I use that path, I get nothing at all. When I change it to the \\mainserver instead of the mapped drive, I get an error. It is a run-time error '76': Path not found

[Mumps1]

I'm afraid that I don't have any experience with mapped drives or servers. Try recording a macro while you open a file on the mapped drive. Then copy/paste the path in the macro that is created into the macros I suggested. Can you attach a couple of year named timesheets. Explain in detail what you want copied and where to paste the data. Refer to specific cells, rows, column and sheets.

[DAULT]

Ok. So I have gotten the rename sheets macro to work. Not sure why it wasn't working before, but it is now, so problem solved.

Now the copy hours macro is not working and I think it might have something to do with me changing the dates of the pay periods in the sheet summary tab. The dates that were being pulled to name the tabs were incorrect (they were using the last day of the pay period instead of the first day. Here is a new timesheet master file that works. I have created a master file with the tabs named with the period name so that the rename tabs macro will work correctly from year to year. When I run the copyhours macro I do not get an error or anything - it just doesn't fetch the data.

[Mumps1]

What do you mean by "new timesheet master file". If you have made changes in the files, please attach copies of the revised versions of the 4 files I attached in Post# 20 so I can use them for testing. Have you resolved the opening of a file on the mapped drive?

[DAULT]

I guess the file is more of a template. It has all of the generic Per labelled sheets so when a new year starts of when a new employee starts the rename sheets macro will still work. Since the macro relies on the tabs to have the PER tab name to work, it will not rename tabs if a previous timesheet is saved as a new one for a new year.

The only file I changed is the timesheet, which I attached. The only thing I changed is the dates in the Sheet Summary tab.

The Copyhours macro does search the timesheets, however it doesn't copy information from them. Here is the Job Cost Ledger I am using with the macro modified with the correct file path. The top button is linked to the rename sheets macro and the bottom to the copy hours macro.

[Mumps1]

Try:

Please Login or Register  to view this content.

[DAULT]

It worked until I created a second timeseheet in the same folder for the next calendar year. Essentially what I have done is created two timesheets for the same person. One for the 2018 calendar year and one for the 2019 calendar year. These are in the same folder so the macro will find all data for all employees for that project.

This is the error: Run-time error '-2147221080 (800401a8): Method of 'Cells' of object '_Worksheet' failed.

Something else I would like this to do is calculate all of the hours in multiple years in the same column for each employee. For example, if one employee has done work on a project in 2018 and 2019, I would like those hours in the same column if possible.

[Mumps1]

You have to make sure that the dates in column B of the Job Cost Ledger match the sheet names in the time sheets.

[DAULT]

Ok. I double checked the tab names and they are all correct. I did delete the second set of dates from the job cost ledger since it really isn't needed and now i have a new error.

The error is: Run-time error '-2147221080 (800401a8): Automation error.

When you run the debug, the error occurs in this line:
wsDest.Cells(6, fBlankCell) = wsSource.Cells(2, 3)

If we can get this to work that would be great. I think the entire thing works as intended other than this error.

Then if we can have it combine timesheets for each employee into a single column, then I think that would be it.

[Mumps1]

Attached are the modified same 4 files. They worked for me using the "C:\Test3\" folder path. Run the ReNameSheets macro first and then the CopyHours macro. By the way, you didn't have to delete the second set of dates from the job cost ledger. The macro will work regardless. Attach a couple of time sheets for the year 2019 for the same 3 attached 2018 time sheets.

[DAULT]

I am really confused as to why this is not working.

What I am doing is taking the template timesheet file with the Per named tabs. I am doing a saveas, running the rename tabs macro (which works) and then running the copy hours macro which gets the same error -even when using your files. I shoudl note that it is when i get to the second file that the error appears. the first file works.

We are so close to getting this to work.

[Mumps1]

When you get the error and click 'Debug', hover the mouse over each variable in the highlighted line of code and note the current value in them. You should be able to spot the errant variable.

[DAULT]

Ok. This morning I did this and received the following information:

[DAULT]

I closed everything and ran it again. This time hovering over the same wsSource.Cells gave me an automation error.

[Mumps1]

I can't reproduce the error. Check that the second file has a value in cell C2. This should be the last name.