Create summary of worksheet data in new worksheet

[Jonny_XL]

Hi,

I would like to summarize all data rows from worksheets 1-3 (could be up to 12) on a "Summary Data" worksheet within the active workbook . Each tab could have a variable number of rows.

The following code works to get data from a single worksheet, but the match.row causes problems when there's more than 1 worksheet. I need to a way to increment the destination row so I don't overwrite data on the summary sheet.

Please Login or Register  to view this content.

I'm also playing with pulling data only if the value in column C is "Delay". This code works most of the time, but it doesn't seem to copy all of the rows that say "delay" , not sure why...

Please Login or Register  to view this content.

Can the code be set up to copy data from a random number of worksheets into one summary worksheet, auto increment the rows for subsequent sheets, and skip blanks rows on the source sheets?

[vcoolio]

Hello Jonny_XL,

Perhaps the following code may do the task for you:-

Please Login or Register  to view this content.

It filters Column C in all sheets for the criteria "Delay" and transfers the relevant row of data to the Master sheet. Add as many sheets as you like and the code will cover them all.

I've attached my test work book for you to peruse. Click on the "Consolidate" button to see it work. You may have to change the "H" in the above code to whatever your last column is.

[Jonny_XL]

Wow, this solution works perfectly, and is such an efficient use of code! You have saved me a lot of effort!

Thanks so much!

[vcoolio]

Hello Jonny_XL,

You're welcome. Glad that I was able to help.

Cheerio,
vcoolio.

[Jonny_XL]

Hi VCOOLIO,

Your code seems to work flawlessly in your sample workbook, but when I use it in my actual spreadsheet, i run into an issue and i'm not quite sure why. I have made various subtle changes to your code, but I made those changes in your attached excel file, and I had no problem.

The issue i'm having is that the value in Col. A of each worksheet is a formula (A2 pulls a date from another cell (e.g., M1) if B2 is not blank). Nothing fancy.

On your master sheet, the value in A2 is returned as text. In my spreadsheet master, the value in A2 contains the formula, and because M1 is not the same on my summary sheet, the date is incorrect.

I don't know why its pulling over the formula in my version, while in yours it's pulling the value of that formula. When i paste my code into your spreadsheet (and change the cell references as required), your spreadsheet still works.

My code is here. Changes are offset, introducing a lookup based on user input, and changing cell reference (col. C is now J, and the data is in col A-L)

Please Login or Register  to view this content.

[vcoolio]

Hello Jonny,

My sample doesn't have formulae but your work book does so we need to make a subtle change to the code just so that values only are transferred.
Hence, change the following part of the code:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

In the part of the code where you are excluding the Master, Admin and Template sheets, you may want to change the Or to And. Or is used more as an option.

Also, I notice that the data transfer starts from row 5 so I assume that your headings are in row 4. If so, place the filter on J4 not J1. and then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Test it all in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.

[Jonny_XL]

Hi Vcoolio, and anyone else,
Finally have a chance to get back to this. However, i'm still seeing weird things.

1) The "TransferToMaster" macro runs, but i've notices in a few cases some data is not copied. It might be related to having blanks in the source data table, because if I add any text in the "Other comments" column for those cells, they are copied to the master. But, it doesn't always miss blanks. (that is, sometimes there is no comment in the "other comments" column, but the sata is still copied.

For example, in the attached file, when i run a search for "Complete", it misses the data from:
April 2016, row 33 (ie, item 9c)
April 2016, row 36 (ie, item 10)
May 2016, row 14 (ie, item 8)

If i run searching for "No Action Required), it misses the one row:
March 2016, row 13 (Item 9)

I could always make sure there are no blanks (assuming that would work), but why is it doing this?

2) If there is no matching field found in the source data of a given worksheet (ie, searching for "complete", but there are no matches in a worksheet), the macro pastes in the header row. No idea why. It's not a big deal as it can be deleted, but can this be prevented? I don't know why it would even look in this field.

3) If there happen to be no status at all on a given page, it pastes row 1 into the master sheet. This is unlikely to occur in real life, but what's going on? The macro should never look in row 1 for anything!

The source code is here, and the spreadsheet set up as per my data/needs is attached (stripped of data, but it responds the same way as my file with real data in the same places...

Thanks for any help!

Please Login or Register  to view this content.

[Jonny_XL]

having trouble attaching the spreadsheet...will try again.

[vcoolio]

Hello Jonny,

I haven't had a close look at it yet but try the following take on autofilter:-

Please Login or Register  to view this content.

Let me know how it goes.

Cheerio,
vcoolio.

[Jonny_XL]

Nice, this seems to do the job! I'm going to test it out more thoroughly, but everything seems to work right now.

I decided i wanted to paste both values and formats, so I changed to include both these lines in place of just xlPasteValues:

Please Login or Register  to view this content.

seems to work, although there's probably a more efficient way...

[vcoolio]

Hello Jonny,

You could change this part:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Cheerio,
vcoolio.

[Jonny_XL]

Thanks for all the help!

[vcoolio]

Hello Jonny,

You're welcome. Glad that I could help.

Good luck!

Cheerio,
vcoolio.