RunTime Error 9 when using "With(strName)" to find sheet "strName"

Following code should find the worksheet matching the name in Col AA, find whatever value is in the last cell of Col D in that sheet, and put it in Col AF of the Active sheet

Tried the following logic:

Set the number of sheets to be checked as the number of entries in Col AA
Set the name in AA (row) as a string, and call the sheet with that name
Find the last row in Col D of that sheet
Set value in row AF in the original sheet as that Col D value
Repeat cycle for other rows in AA

Code recognises "strName" as the name in Cell AA (whatever) but then generates a "Runtime Error 9 - Subscript Out Of Range" at "With Sheets(strName)"

Please Login or Register  to view this content.

All suggestions, solutions and advice welcome as ever

Ochimus

Please post a sample workbook with some typical data.

TMS,

Thanks for prompt interest.

I've shifted the Sheet1 data to Cols A and B (saves everyone trekking pointlessly to Col AA - AD)

Sample has three sheets each showing just the "total" in Col D, each of which are on different rows.

Cols F and G in Sheet1 show what the result should be - they show the fomulae to clarify what I want the Code to do, but obviously the Code will paste the actual hard numbers.

Please Login or Register  to view this content.

Hope that helps

Ochimus

EDITED: Sorry, please amend Macro on attachment. I left it posting the value into Col A, not Col B, which I have corrected on this Code extract.

Do it this way:

Please Login or Register  to view this content.

You find the last row using xlUP which gives the Total row. So, when you loop through the cells, you get to the blank entry before the Total. And that's what fails; you don't have a worksheet with a blank worksheet name.

By using xlDown, it just goes down to the last data entry. And doesn't try to process the blank cell.

You could, of course, use xlUP - 2