Collecting Data from multiple worksheets

I have a workbook that has worksheets labled from the last 2 years. I want to create a macro that will collect data from select worksheets. The "range" of data from each worksheet will vary, but let's say I wanted to collect B2:E15 on each worksheet (this will collect some "blank" rows from some of the worksheets). Example worksheet names are "Jan" "Feb" and "March". I want the data to collect in rows...each new worksheet data to "paste" below the previous months data. I have seen several variations of what I am looking for, but not exactly what I am looking for. I have been able to modify a macro, but it doesn't do exactly what I am looking for. Any help would be appreciated.

I have gone ahead and attached part of my report. Here is what I am looking at doing:

Tabs I want data collected from: Mar 13, Apr 13, May 13, June 13. The data I want collected from each tab is going to have the same cells; however, some may have more rows. So there may be "empty" cells that are collected. The columns from these worksheets that I want information collected from are J, M, O and P all the way to row 30 as that is the most rows I would ever have information in. **These cells are calculated from formulas. I want the information from these cells "pasted special" into the summary tab such that the information from Mar 13 is the first collection of information and "pasted" below this information is the Apr 13 information and pasted below this information is the May 13, etc. etc. etc. with empty cells being written over since the number of rows with information will vary from worksheet to worksheet. So in the end, I have a long string of data on the summary page from select worksheets within my workbook.

Thank you - the code is beautiful. It DOES work in that it brings information together; however, it is not collecting the information I need. I went through the code and changed the line

.Range("A1:I1").Value = Array("sheet", "Date Filed", "Status", "Owner", "Ack letter", "date Days", "Compliance (yes or no)", "Resolution letter date", "Compliance (yes or no)")

to this:

.Range("M1:S1").Value = Array("sheet", "Responsible Owner", "Name/yes&NA (I)", "Number of Cases", "Acknowledgment Compliance", "Name/yes&NA (K)", "Resolution Compliance", "Month")

Since M1:S1 is actually the data that I want complied; however, it is not working. Is there another field I should change as well in the code?

Also - one more question, if you would be so kind: The actual workbook I will be using has worksheets from the last several years; however, I am only going to be compiling information from the most recent worksheets only (all other worksheets can be disregarded). Is there a way in the code for me to explicitly state what worksheets I want information to be pulled from so that information is not pulled from every worksheet?

Again, thank you so much.

The names will just be the months of this year so Jan13, Feb13, Mar13, Apr13, May13, June13, July13, Aug13, Sep13, Oct13, Nov13, Dec13.

Hi,

I went ahead and tried running the modified macro in my acctual report along with the adjustment I made to the range line so that it would pull the data from columns M to S; however, I just keep getting an error and nothing is happening now. I played around with the macro and tried it several different times with several different variations to get the data I need, but nothing seems to work. Maybe you can see what is going on...? I have attached my actual report.

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

make sure to save the file as a macro-enabled, or the macro will not be saved

Oeldere,
It was you code.
You did not notice you used sh instead of ws. I think if you had tested it before posting, you would have spotted the error as the code will not run beyond that line since there is no sh in the array, but ws.

Thanks everyone! It works perfectly now I really appreciate the help...this will really help make things easier at my work. Again, thank you. ~Casey