Subscript out of range (Error 9)

Have you checked the sheet names and the values in the list for things like leading/trailing spaces, spelling errors etc?

Also, what workbooks are open when you run the code?

Not to sure without seeing all your code, but this stands out...
Change

Sheets("" & Plan2.Cells(i, 3).Value).Select

to

Sheets(.Cells(i, 3).Value).Select

Originally Posted by carolmanente

Why is this error occurring?

Subscript out of range happens when you try and refer to an element of a collection/array that does not exist. The gist of it as it pertains to your code is you are trying to .select a sheet that doesnt exist.

To overly simplify, if you have 3 sheets in a file and did:

Sheets(4).select

you should get the same error. If your sheets are named: Sheet1, Taxes, Bob and you did:

Sheets("Earth").select

you should also get the error.

you need to check what:

"" & Plan2.Cells(i, 3).Value

becomes in that line when you get the error. You could add a debug line above it and have it debug.print to the immediate window the value of just """ & Plan2.Cells(i, 3).Value" to see what it is at that stage in the loop prior to failing. Alternatively you could add a break and step through the code and evaluate the value prior to getting the error.

Since we dont know whats in your cells I cant speak to why you have "" & preceding the cell values, not sure what the benefit of adding nothing before that string would be.

Its also generally asking for trouble to have a with inside another with. It should work, but often gets confused the more closely related both objects are. Especially when combined with implied language like Sheets() instead of workbook.worksheets() or .cells instead of sheet/worksheet.cells().

without sample data/workbook its hard to be more specific about the cause of your problem