Excel 2007 : Macro to combine data from multiple sheets into one sheet

Hello All,

I have about 200 worksheets which are all on seperate tabs within the same workbook. What I would like to do is to write a macro takes the last line of every worksheet starting in column B through f and places that data range in a summary worksheet. The data from the first worksheet can go in cell B1 through F1 of the summary worksheet. The data from the second worksheet will go in cells B2 through F2.

Can someone help me write a macro?

Thanks, Bill

Thanks Dom.

I have a more remedial question though. In order to use code that you provided, do I go to cell B2 in the summary tab, hit ALT+F11, paste your code, save it and then run it? I tried that and I got an error. I hit the debug command and it highlighted;

wsEachSheet.Range("B" & Rows.Count).End(xlUp).Resize(1, 5).Copy _
Sheets("Summary").Range("B" & lngWriteRow)

I am running Excel 2007.

Just worked it out. I need to name the summary sheet "Summary". After that the macro ran but it is only corret for about the first 20 lines or so. I am trying to work out why the other 180 are not right. It is pulling data but not from the last line in the worksheet.

Originally Posted by Domski

The macro checks for the last line of the worksheet by working up column B until it finds something. Would column B not always be populated?

Dom

Dom,

That makes sense. Unfortunately, many of the worksheets do not have anything (text) populated in column B in the last line; however, all of the spreadsheets do have data populated in column A in the last line. Can the Macro be changed to look for the last line that has data in column A vs B?

Thanks Dom. Works great!