Creating Pivot Table with data mutliple worksheets - NEED VALUES! (not sum/avg/cnt etc)

[3rp]

I have workbook with worksheets for every day of the MONTH with 5 columns of identicle columns of information.

I need the information from each sheet to be on one but having trouble.

PLEASE HELP!

[RHCPgergo]

Please post a sample workbook and explain more what you would like to achieve. If you just want to copy the data from all the worksheets into one, then it shouldn't be too hard, just a few clicks and hotkeys. If you have to do that task frequently, a macro solution would help you greatly.

[3rp]

Please see sample workbook below. To answer your question, yes I just require the data from each sheet to be on one master sheet for the month. Basicly want to save myself from having to copy and paste each sheet for the month . . . it'd be nice to link the dates each is from but not sure if that's possible.

Thanks in advance,
3rp

SampleWorkbook.xlsx

[RHCPgergo]

I put all the data that was stored in a bunch of sheets to one sheet on the sample workbook. It's a lot better practice than storing it on a lot of sheets, because you can make pivot tables, apply filters, create graphs... and basically save the trouble of having to click through all the worksheets. I suggest having only one worksheet for collecting raw data, you can have others where you work with the data

How many worksheets are there that you have to copy/paste together? I'll try to make a macro for such a thing, but if it's just 20-30 sheets and you only have to do it once, it's quite fast with copypasting, really.

[3rp]

PERFECT! how'd you get the data all on one sheet? For security reasons I removed confidential information that's on my logs so will need to re-create the master list for my file.

I agree completely on your methods but, new to position and trying to convince management to let me change it. The reason I need to avoid copy and pasting is this workbook is for one of 6 people. I really don't want to do it for each workbook. I'm doing this in addition to my daily work to show them how easy the task SHOULD be. Need to work with this for logs from last QTR as well.

[RHCPgergo]

I feel your pain. Try to convince your colleagues to use one worksheet for data, because it makes it a lot easier for them too...

I used copypasting, but I'll make a macro. If the spreadhseets are in the same format, it will work on all of them. A few questions though.
1. Are the sheet names in the same format as in the example workbook? I want to use them to make a column of dates.
2. Are all dates from 2012?

[3rp]

THEY DON'T AGREE! None are really EXCEL users though besides myself.

-Since I was able to create the December log each sheet and columns are identical in naming.

-YES for last quarter of 2012

[RHCPgergo]

Here's an example workbook with a macro that works properly with the data provided. It copies all the data into a single worksheet. I've also added some formatting so it doesn't look that messy. I've also added some comments for clarity. Feel free to adapt it to your needs. Tell me if it doesn't work properly in excel 2007, I'll try to correct it.

I would like to hear what your colleagues think about it. I think you shouldn't present it in an arrogant way or they will refuse it just because.

Minor point:
You might want to apply some conditional formatting (such as coloring cells) on the Approved/Returned column, because it's a little hard to distinguish between the two words at first sight. Or maybe just change it to A for approved and R for returned. Or Yes/No.

[3rp]

I'm sorry I was out on vacation then stuck in training to try it out with my file. WORKS PERFECTLY!!

Can not thank you enough, I was able to modify the macro to incorporate the data from the extra columns in my workbook.

Your time is extremely appreciated.

[RHCPgergo]

Glad to be of help.

[oeldere]

@RHCPgergo

Your macro works well for the Excel 2007 version.

And after running the macro (and adding a column for day and month) you can easly make an pivot table (this is just an example) to combine those info.

See the attached file.