Prefixing Worksheet Name with Pulled Data

I have a macro that pulls data from all of the Worksheets in my Workbook (all the same format, data in columns D to Q) and adds it to the Summary Worksheet without issue. But I want to add the source Worksheet name in column A followed by the data from that worksheet. I've tried it with the "'Range("A:A") = ws.name" statement in the three spots that you see it commented out at and it doesn't give me what I want. It either puts "Summary" (the active sheet) or the name of the last worksheet that it pulls from for all of the data. It also fills column A all the way down to row 65536, which is no good. Can someone point me in the right direction with this one? Here is a picture of what I want:

excel.jpg

Please Login or Register  to view this content.

Thanks for that code AB33, but when I run it, it puts the WS name in column A of the source WS and doesn't copy/paste anything to the Summary WS.

Please Login or Register  to view this content.

Thanks again AB33 for the quick response. This code definitely got closer. But I end up with 1 or 2 extra entries in the "Sheet" column and it offsets the data a bit. I end up with the sheet name for Sheet 1 populating next to data from Sheet 2 on 1 row. I messed around with the offsets in your code but that didn't fix the issue.

excel2.jpg

I knew that. My code was a guess work based on the last row in column D. It all depends on the length of column D. If you deduct 2 or 3 from the last row in column D, that should give you the correct rows.

Please Login or Register  to view this content.

You can adjust this line

Please Login or Register  to view this content.