Looping through worksheets in a workbook to pull data

Hello all. I'm new to the forums, I've been here a few times doing searches on threads and this forum has been a tremendous help. Plenty of very well versed VBA folks here so I figured I'd join and ask a question.

I'm currently writing a macro now that is supposed to pull all files out of a particular folder and toggle through each worksheet in that particular workbook. The number of worksheets are liable to change every month so the macro has to know to look through all worksheets.

On top of that, the macro only needs to extract data from only a select few of those worksheets. Some worksheets have data in range A1:A4. If it does, I need it to copy and paste it into a report format (using paste special, transpose). If the A1 cell is blank, the macro will need to just go to the next worksheet within the workbook. I'm having trouble figuring out how to tell the macro to copy the range A1:A4 when necessary and pasting it into the new workbook. The macro also must know to move down one row for each time it pastes so it can paste new data if necessary.

Writing the macro to open up the files I need isn't a problem. Its just figuring out how to loop through the worksheets, check to see if there is data in range A1:A4, copying the data if there is data there, and then knowing to move down one row each time it pastes the data so it can compile a useful report.

Thanks in advance for everyones help!

Ok, I thought I could figure this out given the help I've received so far but I can't get this to work at all.

I think I need to start the code over from scratch but I don't know how to even start it. I've never had to write code that'll loop through workbooks worksheets and perform a particular task if a certain condition is met within each worksheet.

I'll try to explain the best I can, what I need the macro to do.

I need to start a new Excel file, this will act as the master file that is supposed to collect data from several different workbooks (all in the same file) in order to create a summary report. The data I need to extract will be shown in cells A1-A4 and will tell you the program name, program number (which consists of letters and numbers), person working on the project, and the type of report he or she is working on. Some of the worksheets have this information, some of them don't (they may simply display graphs, others will have text or numbers in cells A1, A2, A3, and/or A4 but never in all four cells at once). Essentially, I want the macro to see if a worksheet has text and/or numbers in cells A1-A4 and if so, copy it. So basically what I want my macro to do is:

1. Open all Excel files in the folder.
2. Go through each worksheet in these Excel files (the Excel files will be updated periodically with new worksheets so I need a code that will allow Excel to check all worksheets).
3. If cells A1-A4 all have text and/or numbers in them, copy it, otherwise move onto to the next sheet.
4. Once copied, it must go over to my master Excel file meant to compile the report.
5. It must paste the data but it has to transpose it so, for example, the first set of data it copies must be pasted in cells A2-D2.
6. The macro must then move down one row to be ready to paste more data (i.e. go from A2-D2 to A3-D3, etc. until all worksheets in all the workbooks have been searched).

Thanks in advance for any assistance you may offer.

Thanks. I used some of your code to help out with what I've put together so far. This is what I've got:

Please Login or Register  to view this content.

All it does is go through and open all of the Workbooks in a folder but doesn't go through the sheets like I need it to.

Excellent, thanks!

Now the macro goes through all sheets in all the workbooks as intended, however, even if cells A1-A4 have data in them, it doesn't extract them...

Heres the code as it stands now:

Please Login or Register  to view this content.

I'm still getting the hang of writing macros as efficiently as I can. I never took a formal class ont hem in college (studied economics and statistics) and now that I'm out int he "real world" I figured it'd be good to know how to write them so I bought a ton of book a little over a year ago and began to read them and practice. Glad I joined the forum, hopefully joining here can further improve my VBA abilities.