|re: "therefore need to identify last created and 2nd last created without use of names"|
VBA doesn't know which sheet was created when, so you have to refer to it by either it's name or number.
As long as the "last created and 2nd last created" are the last 2 sheets in the workbook. Sheet.Count and Sheets.Count -1 should work fine since you are technically referring to them by their number.
You can also refer to worksheets by their name but you don't have to hardcode the name in VBA. You can build the name with a variable.
For example, this code will display the contents of A1 from the sheet named March 2013 anytime during March 2013.
myMonth = MonthName(Month(Date))
myYear = Year(Date)
MsgBox Sheets(myMonth & " " & myYear).Range("A1")
As far as your code, well, there are a number of issues:
As I mentioned in the other thread, you should not have to Select a worksheet in VBA to perform an action on it. Selecting sheets or cells just makes the code inefficient.
I have no idea what you are trying to do here. There is no such thing as Range("L"). If you are trying to refer to Column L, you can use either Columns(12) or Range("L:L").
That said, I have no idea why you trying to activate Column L (or Column M later on).
re: result = Application.WorksheetFunction.VLookup(sheet.Range("A"), _ Sheets(Worksheets.Count - 1).Range("A:l"), 11, False)
I'm not sure where to start with this line...I guess I'll start at the beginning...
You use the variable name "result" but you don't ever do anything with it. If your VLOOKUP instruction were to work, all it would do is set "result" equal to the returned value and then move on. Later, you use "result" again to store the value from a second VLOOKUP, but you never really use the value stored in "result" for anything. There's not much point in setting a variable to a value without using it for something later on.
This doesn't mean anything to VBA.
sheet has to refer to an actual sheet, e.g. Sheets("MySheet").Range(...) or Sheets(2).Range(...)
Range("A") doesn't mean anything to VBA. It has to be something like Range("A1") for a single cell, or Range("A:A") for an entire column or Range("A1:C16") for a range of cells. Since this appears to be the first argument of the VLOOKUP, it must refer to a single cell. It has to be something like this, with a valid sheet and cell reference:
Finally, this is just a pet peeve of mine. Sloppy coding is hard to read and follow, not just for the author, but even more so for someone trying to read another person's code.
For example, these 2 lines:
Ignoring the fact that these lines don't work, in one line you use an upper case letter, in the other you use lower case.
Sheets(Worksheets.Count - 1).Range("A:l"), 11, False)
In that line you mix upper and lower case within the same parenthesis. Range("A:l")
All cell references should be in upper case for neatness and readability. While some things may work regardless of the case you use, other things will be case sensitive. Sloppiness in these areas leads to sloppiness throughout the code and that can lead to actual errors.
Finally, if you are going to write VBA code, you should know how to troubleshoot VBA code. Please read the VBA Troubleshooting How-To found via the following link: It will not only help you troubleshoot your own code, but it can be very helpful when trying to reverse engineer code that you find on the web or in someone else's workbook.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.