Loop not running on all worksheets

I attached a sample file. Adjusted code is below to change the offset from (0,-7) to (0,-2). I tested with a cell on Sheet "1125" activated, and the sheet name "1125" was inserted in the appropriate cells in Column C, and the macro continued to create the "Consolidated" sheet and consolidate the data onto it. However, because only Sheet "1125" had values in Column C, all the other cells in Column C on Sheet "Consolidated" are blank (no data on source sheet, therefore no data on consolidation sheet).

Please Login or Register  to view this content.

The new code returned a couple of errors. The first said "Excel cannot complete this task with available resources. Choose less data or close other applications." It also gave Run-time error 1004 - "Copy method of range class failed." It was caused by the last line of code below.

Please Login or Register  to view this content.

When I paste the code in , I get a compile error: syntax error on the second line of the code above.

The pasting of values into Column C is not based off of a specific column. Each sheet has a header in Column C. The sheet name needs to be pasted below that, down to the last row. However, the last row may be determined by any of the other columns (i.e. the last row may only have data in Column A, or only in Column B, or in Columns A and D, etc.).

I think we're straying off topic. The code I listed in my original post to paste the correct values in the correct place was working just fine, as was the rest of the code. The only problem I am having is getting the first part of the code to loop so that it runs on each worksheet (inserting the sheet names into Column C). It will only run on the active sheet. I can make it start at the first sheet, but then it will only run on that sheet. It does not loop and continue to the rest of the sheets in the workbook before it compiles all the data onto the "Consolidated" sheet.

I'm also finding errors with your original code further down (Sheets(2).Range("A1")...) once I replace my original code to paste in the sheet names. As I said, my original code works fine for everything except getting the first part to loop. I think the problem is with my For Each loop. If I remove the End If, I get a Compile Error: Next without For. I'm not sure how to fix it, because the code starts with For Each. Is it possibly a nesting issue? Use the attached file and the adjusted code below:

Please Login or Register  to view this content.

OK, so what I was trying to say about there not always being another specific column to reference as far as pasting the sheet name, is a very important piece of information. When I took out pieces of data, the code did not work correctly, because it's trying to reference a specific column (still doesn't work if you change the column from B to any of the others). I've attached a better sample file, one in which the last cell (Ctrl+End) does not match the row in which Column B (or any other reference column) has data. The code I previously posted goes to the last row with data (regardless of column).

The looping part worked, but once I changed the data (see attached file), the code did not work properly.

It works, but not when I start taking data out of the last row (see the V3 file I attached). It is unlikely that, in reality, the last row on every sheet will have data in every column, so no, there is no column to reference (hence my original code). That's why the V3 file is a better test file. I'm sure there's a shorter way to code what I did, but I'm not sure that's the issue. The issue is that the loop around that part of the code isn't working properly.

Bump - No answer in 3 days. I think the person who was working on this hit a roadblock.

Consolidation Macro Test V3.xlsmSorry - not sure what happened. Should be there now.

Works like a dream. Thanks.