Copy Data From Every Sheet To A Master Sheet

[kimi4989]

Macro Help.xls

I have a workbook with 34 sheets. I have 1 sheet per school in my district (33) and then a Summary sheet. I want to be able to update the data in each individual school sheet and it automatically update the summary sheet. I will not be adding data to each school sheet on the same days/at the same time/at the same frequency. In other words, I might add 1 row of data to the School 1 sheet today, 3 rows of data to the School 7 sheet today, 8 rows of data to the School 20 sheet tomorrow, etc but I want the Summary sheet to always be up-to-date. How do I do this? I have tried some of the suggestions I have found on here but can't seem to get it to do what I want. Does my request make sense? I have uploaded my file if anyone has any suggestions. Thanks in advance for any help!

Kimi

[oeldere]

See the attached file.

I made this with an macro (see the sheet consolidated).

Please reply.

[kimi4989]

oeldere,

Thank you. That is almost exactly what I am looking for with one exception. Is there a way to not have rows already designated for each school on the consolidated sheet? Is there a way to run the macro where it automatically knows to move to the next available row? I know that I can eventually run a sort by date but I am looking for
automation. Example: row 1 might be School 1, row 2 might be school 11, row 3 might be school 30, row 4 might be school 1 again, row 5 might be school 1 again, etc. It would all depend on when they submit a request to me. Thank you for your help!

Kimi

[oeldere]

In the consolidated sheet you get all data in one sheet.

Then you can easily analyze the data.

In that case I find it very easy to know from which sheet the data comes from.

If you don't want that info you could (eventualy) hide that column.

I can delete that info from the macro (but I think it's usefull).


You can activate the macro with an button (if we add one on the consolidated sheet).

If I read your question you want to sort the data on date.

Is that correct.

Please reply.

[kimi4989]

More Macro Help kimi4989 (VBA oeldere) (2).xlsm

oeldere,

You are so helpful and I am very appreciative! Hopefully this will be my last question. Is there a way for you to set the macro to automatically sort the data on date? If there is a way for you to edit the macro to automatically sort the data on date (oldest to newest) then my next question is can you tell me how to edit the column labeled "Balance"? As of right now it is copying the balance from the individual school sheets over to the consolidated sheet which is what I asked you to do but I decided that columnjust does not make sense on the consolidated sheet. I would like to have to show a beginning balance of $238,029 and then have each expenditure that copies over from the school sheets "total price" column automatically subtracted from the beginning balance and then each expenditure subtract from the previous one to keep a running balance. Does that make sense? Is that possible? I have uploaded the same file with an example of what I am talking about.

Kimi

[oeldere]

I'm intrested in what you want to achieve with the data on 1 sheet.

Maybe a pivot table, will be helpfull for that.

[kimi4989]

oeldere,

I work in our local school system and need to keep track of how each school in our districtspends the funds they are allocated in one of our budget line items. I need to be able to quickly, at-a-glance track by school and as a whole district as well as by type of expenditure by date. It needs to be a spreadsheet I can print out for the state auditors as well.

[oeldere]

See the attached file.

You can push the button to get all data and also sort the data.

The range of the sort is till row 2.500. If you have more rows, you have to change the macro.

After that I made an pivot table of the data.

Please reply.

[kimi4989]

oeldere,

That is perfect! Thank you so much for your help!

Kimi

[oeldere]

Thanks for the reply.

Glad I could help.

You can add rep(utations) to the ones who helped you by clicking on the star.

[kimi4989]

Hi Oeldere! Are you able to help me with this excel file again? For some reason, the macro it is not pulling data from all rows (that have data) in all active worksheets into the "Consolidated Sheet". For example, there is data on rows 2-22 on one sheet but the macro didn't pull the data from rows 5-15. One sheet has data only on rows 2 and 3 and they aren't showing on the Consolidated Sheet at all. Can you help me? I am not sure what I need to modify. Thanks!

[oeldere]

Difficult to say without seeing your file.

Maybe there are no data in column A?

[kimi4989]

Excel Help.xlsm
oeldere-
I have attached the file. Everything else (formatting, etc) is just the way I like it except for I can't get the data to refresh in the table. Any suggestions?

Thank you so much!!

Kimi

[oeldere]

You got trouble with the data in the consolidated sheet

OR

You got trouble with the pivot table.

It is not clear to me, (also not with the example you added).