Macro to Consolidate Data from Multiple Worksheets

[avanbuskirk]

I have about 500 worksheets in one workbook with information about various companies. I want to copy and paste the contents of cell A1 from every worksheet to a column in one summary worksheet. I also want to be able to copy and paste the contents of a cell from every worksheet to a column if it contains certain text. For example, if there is a cell in Sheet 1 that contains the text "City:" paste the entire contents of that cell to the summary sheet column B.

Help!! Thanks!!

[davesexcel]

Check out the example using this code.

Please Login or Register  to view this content.

[avanbuskirk]

Thank you so much for your help! The only problem is I want the macro to return the value of any cell in an entire worksheet if it contains the text "City". I want the destination to be a column in my "Total" worksheet. So I end up with a column, with how ever many rows there would need to be to return every occurrence of the word city in any of my worksheets.

Do you also know how to return the contents of a particular cell from every worksheet to a column in the "Total" worksheet? For example, I have 500 worksheets and all of them contain company name in cell A1. I want cell A1 from every worksheet to be linked or copy and pasted to a column in my "Total" worksheet. So I end up with a column of company name information. For example column A of "Total" worksheet has AT&T in column A row 2, Amazon is in column A row 3, and so on.

Thank you!!