VBA code to reference worksheet second from the end

[kwagner1222]

I am trying to add a vlookup code to a cell and have to reference the worksheet to the left/second to last sheet in the workbook. The name will change since it's a date, not always the date
prior because of the weekend. Some things I tried are below, none worked ...

Dim sh As Worksheet
Set sh = Sheets(Sheets.Count - 1)

1) Range("Z6").Formula = "=VLOOKUP(B2,'[LISR Formatting_Macro - WIP.xlsm]sh'!$B$6:$AB$150,26,FALSE)"

2) Range("Z6").Formula = "=VLOOKUP(B2,Sheets(sh)!$B$6:$AB$150,26,FALSE)"

3) Range("Z6").Value = WorksheetFunction.VLookup(Range(B2), Workbooks("LISR Formatting_Macro - WIP.xlsm").Sheets("sh").Range("B6:AB150"), 26, False)

4) Range("Z6").Value = Application.VLookup(B2, Sheets(sh).Range("B6:AB150"), 26, False)

5) Range("Z6").Formula = "=VLOOKUP(B2,Sheets(sh)!$B$6:$AB$150,26,FALSE)"

Worksheets: I add one to the end with the current date and reference the day before/worksheet to the left. One is added each workday.

Any ideas on how to get this vlookup formula to work and reference the tab to the left? Thank you!

 

[John_w]

5) Range("Z6").Formula = "=VLOOKUP(B2,'" & sh.Name & "'!$B$6:$AB$150,26,FALSE)"