Glad to help.
The error occurs because the "subscript" is undefined. An array has two parts: NameOfArray(IndexIntoArray). The subscript is inside the parentheses and is a reference to one of the elements of the array.
Excel has the ability to translate strings like "Sheet0" into numbers representing the position or order in which array elements are stored. These numbers begin at zero and increment by one until each element in the array has a reference.
You can substitute the word "collection" for "array" and "object" for "element", since we're talking about worksheets.
If the string is not a member of the collection (in this case, worksheets), Excel has no number for it. (I'm not sure about the technical value returned, but in general, a subscript out of range error means that the number in the parentheses is outside of the range 0-n, where n is the number of items in the collection, less 1.)
I can think of two reasons why you got the error:
- The name on the worksheet tab is not Sheet0
- The name looks like Sheet0 but has a space or the zero is a capital O
Since your last bullet point states that the VBA Editor correctly identifies the worksheet name, I would simply retype the code statement AND retype the worksheet tab name.
In rare instances, the workbook can become corrupt. That would require you to do a Clean Project with a 3rd-party tool like MZ-Tools, but I admit, that's a stretch...
One last thing, Thisworkbook always refers to objects in the same workbook as the one where the running code resides. Without seeing your set-up, I have to toss that out there. Make sure you're not running this macro from your Personal Workbook, an add-in, or some other workbook where Sheet0 does not exist.
p.s. don't forget to adjust the range in the following statement:
since your data is in columns A-D, just to be safe.