Transferring data from several worksheets to one "summary" worksheet in the same workbook

[smasian]

Hi,

My company is implementing new pay plans for our employees. I have created a workbook where each employee will have a worksheet that will automatically calculate how much they will be paid for each pay period. However, for internal purposes, we would like to have some of the fields transferred to another "summary" worksheet.

I have attached a sample. I would like to have the green highlighted portion from each of the worksheets transferred to the worksheet titled "Payroll Sheet".

I have been reading up on VBA and Macros, but I am not too familiar with it still.

Could someone please help me with this?

Thank you in advance!

[Dimitrov]

When you say that you want the green portion of each individual's employee transferred to the summary worksheet, is there a way you want the summary worksheet ("Payroll Sheet") to appear? Do you have a sample non-blank Payroll Sheet that could show us how you would like things to be visually laid out? Knowing the layout would help us to write a VBA procedure that would cycle through all the individual tabs and transfer the green portions to the Payroll Sheet.

[alansidman]

How do you want the data arranged in the "Payroll Sheet." Is it just a copy and paste into the next available row going down the spreadsheet? Help us here.

[oeldere]

I ran the macro below.

See the attached file.

Please Login or Register  to view this content.

[smasian]

Pay Plan Template.XLSYes! I am sorry I was not clear. I uploaded a Payroll Sheet that is not blank.

I would like to have the green box from each worksheet transferred over to the Payroll Sheet. I'd like to have as many fit on one page as possible....

Thank you! I appreciate your guys' help!

[oeldere]

What about the solution in #4?

[smasian]

would it be possible to have the Consolidation worksheet update automatically when the information in the other worksheets are changed? Would I just run the macro everytime? I am sorry, I am not that familiar with macros so I do not know exactly how it works....

Thank you!

[oeldere]

I could add an button to push, to update the data.

But what are you going to do with the data on the consolidated sheet?

[smasian]

I would just use that data to input it into our payroll system.

Would it be possible for you to walk me through what you did to create the macro? I would like to do it on several other workbooks also.

[smasian]

What about the formatting? Is it possible to keep the formatting the same also?

Thank you in advance for all your help!

[oeldere]

We can also change the format, but first please respond if the data on the consolidated sheet is the result you want.

Or you don't want e.g. the values with the totals?

Please show in an excel file how the sheet consolidated has to look, so you can inport the data into the pay-roll system.

And did you test the import of data in your pay-roll sysgtem?
There has to be an format which is neccesary for the import?

Please reply.

[smasian]

Yes, the data on the sheet is what I'd like. However, it's going to change from pay period to pay period. I will need to make a copy of this workbook and change the name every pay period. For example, it would say "Tech Pay 8-15" one pay period. Then for the next payroll, I would make a copy, rename it "Tech Pay 8-31", and update the information with the new data.

I would like the consolidated sheet to look exactly as it is in the consolidated sheet sans the highlight. Also, there is not a necessity for it to be in a certain format because we are not going to be importing the file, just the information. It's going to be a manual input.

[oeldere]

Please Login or Register  to view this content.

1) Where would you add this?

2) Isn't it possible to import the data; that would be much easier and avoid faillure's.

3) what is the use of change the format, pure visual ?

[smasian]

It would be added to our payroll files on our in house servers. We can't import the data at the moment because we are on a new payroll system that still needs to have to be ironed out before we move onto importing data. Furthermore, a lot of the employees in this department are not very computer literate. That's why the data has to be easy for them to read and so that is why I'd like to keep it in the same format.

[AB33]

The names of the sheet will not affect how the code runs. The only thing needs to change is the name of the consolidation(Master) sheet.
You can add/change the names of the sheet to what ever you like. All you have to do press the button and code does the job for you.

[smasian]

Thanks! But is there a way to keep the formatting?

[oeldere]

Maybe like this?

See the attached file.

I made an button to activate the code.

Please reply.