Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Vlookup


resolvedResolved · Medium Priority · Version 2007

Edited on Thu 21 Jul 2011, 13:40

Gari has attended:
Excel VBA Intro Intermediate course



I am trying to write some code that will allow my sheet that is running this sub-procedure to look at the 6th sheet of my workbook and perform a vlookup on it.

My issue is that the name of my 6th sheet will change daily (it is format DD.MM.YY - e.g. today is called 20.07.11, tomorrow will be 21.07.11), as it contains the previous sheet's data... how do I reference the 6th sheet of the workbook (The workbook is called "SC_Pending - GD Test" at the moment) ?

We're not sure, but we think it may be an issue with my Dim?

Any help would be MUCH appreciated, as this is driving me crazy!



Dim NextSheet As Variant

Set NextSheet = Workbooks("SC_PENDING - GD TEST.xlsm").Sheets(6)

StrLastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
'creates StrLastRow

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-33],NextSheet!C[-33]:C,34,FALSE)"
Selection.AutoFill Destination:=Range("$AH$2:$AH$" & StrLastRow)


End Sub

RE: vlookup

Hi Gari

Thanks for the question

Can you confirm the error number and message that occurs when you run the code?. Also which line the code breaks on



RE: vlookup

Hi Stephen,

I've had to remove the code and try to do this with static sheets, as I needed to get this launched - so that's been a bit of a stop gap (it copies sheet 6 into a sheet permanently called 'Today's Data').

I would still like to try and get it working this way though as these static sheets are proving a bit clunky.

The code has been modified since I submitted last week and is now as below:


Dim PreviousSheet As Variant

Set PreviousSheet = Worksheets(6)

strLastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
'creates StrLastRow

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-33],'Worksheets(6)'!C[-33]:C,34,FALSE)"
Selection.AutoFill Destination:=Range("$AH$1:$AH$" & strLastRow)


When we run this, we get 1004 - AutoFill Method of Range Class Failed (but I suspect this is because I just hit cancel when it opens a windows brower that asks us to navigate to the workbook and worksheet that we want - which shouldn't be doing, as I've already told this via DIM?).

Many thanks for any help with this!

RE: vlookup

Hello Gari,

I notice you are referencing the index number of the sheet, your code is
Set NextSheet = Workbooks("SC_PENDING - GD TEST.xlsm").Sheets(6)

Try replacing the number 6 with the actual sheet name, if you look in the property panel for Name (top entry), it will say Sheet4, or Sheet12 etc..), in your code put this name in.

Set NextSheet = Workbooks("SC_PENDING - GD TEST.xlsm").Sheet3

Remember this name is the code name and not the name you see on the sheet tab, such as your changing date.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.

Microsoft Office Specialist Trainer


Training courses


Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Excel tip:

Importing Numbers in Excel 2010

Occasionally, when importing data into Excel you find that the some of the imported values are treated as text.

To convert these numbers to actual values, click on an empty cell and press Ctrl+C.

Next, select the range that contains the values you need to change and in the Clipboard Group on the Home tab, click the Paste drop-down arrow and choose Paste Special. In the Paste Special dialog box, select Add and then click OK.

View all Excel hints and tips

Server loaded in 0.07 secs.