VLookup to 2nd last created worksheet while in latest

March 8, 2013 at 04:31:13
Specs: Windows XP
I got a spreadsheet where I add a new tab every month so what I want to do is automate so that when a new worksheet is created, in Column L of it, I populate resutls by looking up ref in column A against ref in last created worksheet range a:M & return results of column L to the new worksheet if any. & if anothing found (iferror) like, I want blank. I have done a bit of code but not sure where I have gone wrong.

eg in column L of the newest sheet I want to vlookup(newestsheet!a2,secondoldestsheet$a$:$m$,11,false)

sheet names are for illustration as names will change therefore need to identify last created and 2nd last created without use of names

I am a newbie & am working hard to get somewhere & with your help hopefully I will strt getting the hang and improve soon.

Thank you

Sub CopyComments()
Dim result As Variant
Dim sheet As Worksheet
result = Application.WorksheetFunction.VLookup(sheet.Range("A"), _
Sheets(Worksheets.Count - 1).Range("A:l"), 11, False)
result = Application.WorksheetFunction.VLookup(sheet.Range("A"), _
Sheets(Worksheets.Count - 1).Range("A:l"), 12, False)
End Sub

See More: VLookup to 2nd last created worksheet while in latest

Report •

March 8, 2013 at 10:09:02
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.

Sub BuildSheetName()
 myMonth = MonthName(Month(Date))
 myYear = Year(Date)
   MsgBox Sheets(myMonth & " " & myYear).Range("A1")
End Sub

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.

re: Range("L").Activate

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.

Report •

March 12, 2013 at 02:40:28
Thanks for the response. My code could be very wrong and believe me, I'm working extremely hard to understand the codes and to do meaningful construction. From your response it looks that I what I need to do is use the sheets count to dentify the second last created sheet. I'm working on it & also trying to trawl the net to find a code I can reverse engineer.

In short what I want to do is create a new tab with details whose structure will be the same as the preceding. In that new tab, in colmn L I want to look up column A against column A in the tab before, then return results of column L if any.

Report •

Related Solutions

Ask Question