Consolidate multiple worksheets into one

Hi gurus,
I am sure this has been done before and I just can't find it. I have a workbook that has several sheets in it with various rows of data on each sheet. I would like to have a macro to loop through all the sheets in the workbook and copy and paste the rows into a new summary sheet. The rows to be copied should only have data in Column A, in other words if Column A of a row is blank I want it skipped. Also Row 1 of every sheet contains my headers, and I am only using columns A through M. It needs to be able to copy rows even when autofilter is in use.

Thanks,
Rachael

Like this?

Please Login or Register  to view this content.

hi how would this code be changed to copy more than one column say column A through to column M and rows 1 to say 200.

Sorry Jaime that didn't work, it still copies some of the rows where column A is empty. Would it matter if my data is arranged as tables?

No, now that I look at this, I believe this loop will not function properly:

Please Login or Register  to view this content.

Once a row is deleted, the next row that the loop should hit would be the same as the prior row number.
The way the loop works, if Row 2 had a blank cell in that column, that row would be deleted, and the entire table would shift up one row.
The next row that the loop should hit should be Row 2 again to check the next item, make sense?

I'll modify today when time allows.

John,
I get a compile error on yours. Looks like it is missing an "End If" statement but when I add that it says "For without Next"

I found Ron de Bruin's code to merge, which works pretty well except I would like to modify it only take the rows that have values in column A.

Please Login or Register  to view this content.

Originally Posted by raeray38501

John,
I get a compile error on yours. Looks like it is missing an "End If" statement but when I add that it says "For without Next"

I caught the error and saved it. You probably looked at it before the changes.

I'm sorry John,
I am still getting an error. Compile error "Next without For"

Oops.

Please Login or Register  to view this content.

One of those days.

John,
I am getting an error on another line now.

"ws.Range(Cells(i, "A"), Cells(i, "M")).Copy Sheets("Summary").Range("A" & Rows.count).End(3)(2)"

Thanks,
Rachael

What is the error? Did you create a Sheet "Summary".