Create Macro to move values from the current sheet in which the macro is activated

Hi Everyone,
First time user here, and pretty new to the Macro element of Excel (and by pretty new, I mean a few hours). What I'm trying to do is pretty simple, I believe. Here is the situation

1. I have several sheets, that have within them summing totals. What I'm trying to do is create a macro that, once I press it on a given sheet, it will take the values from certain cells in THAT sheet and move them into a preformatted sheet, named "End of Day Reports". I averaged some of the values from the original sheet when displaying in the "End of Day Report". The preformatted sheet's name will not change, but the sheets that the data is coming from will be different each day. I though it would be easiest to create a macro button in each sheet, that will reference the same macro, moving the values from the cells within that sheet to the pre-formatted sheet.

I tried to use the macro-record function to accomplish this. As I feared, the macro references the sheets by name, which I think is my biggest problem. I have posted what I did so far below:

Please Login or Register  to view this content.

There might be some superfluous commands in there, as I may have clicked around more than I needed to, and made some mistakes with the formulas. Any help anyone could give me would be extremely welcome. I don't know if there is a way to tag the sheet you are in as the "active sheet", or if there is a way to reference the previous sheet, as I'll only be pulling from one sheet and sending it to another. Not sure if those are possible, I've tried googling it but had very little success, so figured I'd talk to the experts. If someone can't help me with my specific sheet, perhaps they could point me to a macro that seeks to accomplish a similar thing? Thank you so much guys, hope to talk with you soon!

Matteo

(edit) I've done a more through reading of the rules (I apologize, I should have done so before) and have made some changes to my post. Additionally, here is a sample worksheet. As I mentioned earlier, I'd like the macro to transfer data from the active current sheet (there are two sheets in the worksheet I have attached, but there will be 20 in the final one) to the last sheet, which will always be called "End of Day Reports"

Thanks again!

Hi Turkish1801,

I had a similar issue when creating a stock replenishment spreadsheet for a large client at work, I ended up solving it by defining the active worksheet as a variable using:

Please Login or Register  to view this content.

Then when I need to switch back to the sheet that I've defined as a variable I'd use:

Please Login or Register  to view this content.

Hope that helps, I'm a novice at best but hope that puts you in the right direction!

Thanks

Chriz