Copy formulas down a summary page to individual time sheets

pbebow

New Member
Joined
Feb 15, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
I keep track of payroll for my father in law's Care Workers, I have weekly time sheets set up for the whole year, then I have the summary page at the end (I've tried in the same workbook and a different workbook). The summary page references back to each weeks payroll to get the date and each person's weekly hours. I would like to be able to have each week automatically go to the correct worksheet without having to manually enter in everyone. I want to copy and paste it down the sheet and have it reference the correct worksheet, if that makes sense. Currently I type in =then click in the correct spot on the worksheet for that persons total hours and hit enter. But I have to do it for each employee every week and would prefer to have it automatically do it for me.

Screenshot payroll.png
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
then it pops up a second code box and they both look the same but when I run the macro it does the same as the first time, asking before everything.
Can I ask you about this in more detail.
What code box?
It'll be great if you can share an image.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Maybe it'll be fastest to delete all the modules you have, insert a new one, copypaste the code in #14, and then run the macro.
 

pbebow

New Member
Joined
Feb 15, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
Can I ask you about this in more detail.
What code box?
It'll be great if you can share an image.
Ok so honestly I have no idea what I'm doing... I reopened the file so it was where we left off with the first code. I deleted all of the weeks that weren't filled in on the payroll page like you said to do. I clicked on Developer and Macro, then Edit?
step 1.png


Then it came up with these 2 pages open.
step 2.png


If I delete instead of edit it comes up like this
step 3.png

so I try physically deleting the code and replacing it with the new code but that's when I got the error before.
I'm sooo sorry that I'm so
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok I got it. You pasted the code into sheet modules.
In the Project tab in the code tab, you have the Microsoft Excel Objects section and the Modules section, right?
You pasted the code into a worksheet in the former section, which is why it didn't work.
The code must be in a STANDARD module, not in a SHEET module.
 

pbebow

New Member
Joined
Feb 15, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
Ok I got it. You pasted the code into sheet modules.
In the Project tab in the code tab, you have the Microsoft Excel Objects section and the Modules section, right?
You pasted the code into a worksheet in the former section, which is why it didn't work.
The code must be in a STANDARD module, not in a SHEET module.
Um OK, honestly not sure what all of that means. Can you walk me through how to delete the first one completely and do the second one completely, like step by step?
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In the Microsoft Excel Objects section, open the Sheet55 and Sheet56 modules.
I think you have the code in those sheet modules, but you can erase it altogether.
If you get rid of the code in the worksheet modules the right way, you should only have the Payroll macro in Macros in the developer tab.
So if the macros other than Payroll are gone in the image, you did it the right way.
step 1.png


And if the macro runs properly, it should ask you if you'd like to proceed to the next week, if there's more than one set of data available for output.

Capture11.PNG


Once you're done with outputting the data you want, hit "no" when the message box asks you if you'd like to proceed.
With this function, you don't need to delete worksheets.
I hope this does the trick you needed?
But remember that data which have already been output can't be output again. (if you need a function to modify outputted data by a macro, you'll need another distinct code.)
But you'll just need to delete the relevant data on sheet "Payroll" the way you did to test the code in #14.
I hope this isn't too much of a hassle for you.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Ok sorry actually there's a faster way to do that.
step 1.png

1. In this dialog box, select Sheet55.Payroll or Sheet56.Payroll and press Delete (ignore the red square in the image)
2. Do the same for the other
3. Done
 

pbebow

New Member
Joined
Feb 15, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
Ok sorry actually there's a faster way to do that.
View attachment 32481
1. In this dialog box, select Sheet55.Payroll or Sheet56.Payroll and press Delete (ignore the red square in the image)
2. Do the same for the other
3. Done
Ok thank you for all of your time. I do appreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,441
Messages
5,642,133
Members
417,258
Latest member
amk1979

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top