Copy data from specific range on all worksheets, paste onto summary page

[xlyfe]

Hello,

I've got a doozy here.

I have multiple workbooks that contain varying amounts of worksheets within each book.
In each workbook, I have a "Summary" sheet that I need to automatically gather data from a specific range ("A5:G1000") from each worksheet. This represents a table of data 7 columns long.
The problem is, the data within each sheet ("A5:G1000") that I need to copy to the "Summary" sheet, could consist of only 2 rows of data, or it could be 100 rows or data, or even eventually, 1000 rows of data.
The end goal is to have a table on the "Summary" sheet ("A?:G?") that is summarizing the data from the varying sized tables of information from every other sheet in the workbook.

I have a programmatic list of actions below that I need this VBA to achieve.

1) Find each worksheet in the workbook not named "Summary", and then loop through the range ("A5:G1000") looking for any value that is Not Blank, or greater than zero. > 0.
2) Copy all rows of data from range ("A5:G1000") that are not blank.
3) Paste these values into the "Summary" sheet, starting with the first blank cell from ("A2") down. ("A1:G1") is my header row.
4) Lastly, this collection table on the "Summary" sheet needs to overwrite itself automatically whenever the workbook is saved.

One thing to note is that the worksheets within each workbook are ever changing. Meaning, new worksheets will be added to each workbook overtime.

Now to share what I've done so far. Here, I've basically been able to achieve what I'm looking for except that I can only get 1 cell to copy over to the "Summary" sheet.
I need the entire range of data ("A5:G1000") that is not blank, or greater than zero, to copy over, not just ("A5") as I have shown below.
Also, I'm showing this Sub being attached to a CommandButton for testing purposes, but I would like for this to happen automatically in the background without my end users needing to intervene.

Please Login or Register  to view this content.

Thank you kindly to anybody who is able to help me expand this code to include a set number of columns in a range with varying rows of data.

-Mike in Cali

[JLGWhiz]

I am not sure if you are misusing Workbooks for Worksheets in your narrative, but the code indicates you only have one workbook with multiple worksheets. See if you can use the code below.

Please Login or Register  to view this content.

[xlyfe]

Hi JLGWhiz. This didn't work. I'm getting an error at Ln 9, Col 1.

I'm currently in the process of making a dummy workbook to share that should replicate an extreme example of what I'm trying to achieve.

Thank you so much for your efforts.

[xlyfe]

Ok. I've just uploaded a dummy file for testing purposes.

I've included 3 worksheets in this file. The real world example will have many more sheets in the workbook. Each worksheet not named "SUMMARY" has data located in "A5:G?".

I'm hoping that I can copy/paste that data onto the "Summary" sheet in a table like format starting with cell "A3".

[xlyfe]

Any other advice on how I could approach this?

All words and tips are greatly appreciated. Sometimes just bouncing ideas off you guys helps me figure this stuff out.

[JLGWhiz]

I thought you meant there would be blank rows between populated rows in you source sheets.
This should work.

Please Login or Register  to view this content.

There is not enough information to write the code so that it will open the various workbooks and run the code for each one. If they were all in the same directory and were the only files in that directory, it would be pretty simple to do. But if they are in different directories then it would take some work on your part to list the directory paths and workbook names so that a loop could be initiated to walk the list and open and close the workbooks as needed.

[AB33]

Try the attached.
Evertime you save the file, the code auto runs, by first removing existing data and repalce them with fresh data.

[xlyfe]

Try the attached.
Evertime you save the file, the code auto runs, by first removing existing data and repalce them with fresh data.

This is genius!!! Exactly what I've been trying to achieve on my own for the past 8 hours.

Only one thing, where do I modify your code at so that it starts the "pasting" on the "Summary" sheet starting with cell ("A3"), instead of ("A2')?
I am going to have 2 permanent headers on the Summary page so I need to keep Row 1 and Row 2 free.

Also, how easy it for the newly copied data to be pasted and filterable as a table? Is that even possible?

Thank you SO MUCH for helping me out with this AB33.

[xlyfe]

This is genius!!! Exactly what I've been trying to achieve on my own for the past 8 hours.

Only one thing, where do I modify your code at so that it starts the "pasting" on the "Summary" sheet starting with cell ("A3"), instead of ("A2')?
I am going to have 2 permanent headers on the Summary page so I need to keep Row 1 and Row 2 free.

Also, how easy it for the newly copied data to be pasted and filterable as a table? Is that even possible?

Thank you SO MUCH for helping me out with this AB33.

Nevermind on the Offset question, I figured that part out. But what about making the newly pasted data a filterable table on paste?

[xlyfe]

Thanks again for all your help guys. I ended up figuring out the rest. Like converting the summarized data into a table, and then formatting that table. Also, I decided to remove the "update table on save" feature, and instead just associated the action to an update button.

Here's the final product for anybody looking for help on something similar to this.

Please Login or Register  to view this content.

[JLGWhiz]

Thanks for the feedback,
Regards, JLG