sheet name variable not working in vlookup

[smak]

I have multiple tabs and will be moving from one to the next. so I need to have a variable that has the tab name...so that I can use it later.
1. saved the tab name as a variable
2. want to use that saved tab name in a vlookup
2a. the tab name is alaska
2b. the chart that is being pulled from has alaska as its main filter


but when I try to use my variable name in the vlookup formula, it shows up as all caps (ALASKA) and it get a #NAME error

Dim TabName As Variant
TabName = ActiveSheet.Name

Range("J1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(" & TabName & ",'summary sheet'!C1:C10,10,FALSE)"

I am sure it is just a small typo that is causing the issue. thank you in advance

 

[Joe4]

Welcome to the Board!

Note that you are NOT using R1C1 reference, so this part:

    ActiveCell.FormulaR1C1 =

should just be:

    ActiveCell.Formula =

If that does not solve it, can you show us what is in cell C1:C10 on the 'summary sheet'?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

I see another error in your formula I missed initially.

The third argument (10) is saying to return the 10th column of range C1:C110.
That is just a one column range, so that won't work!

If you really want to pull the value from column L, it should look like this:

Range("J1").Formula = "=VLOOKUP(" & ActiveSheet.Name & ",'summary sheet'!C1:L10,10,FALSE)"

(note how all your code can be reduced down to a single line of code).

 

[smak]

thank you for the help. I will definitely use that formatting in the future for other code. most of what I know has been 'self taught' so I don't always get to see some of the finer parts of VBA.

I tried the different solutions you gave but they did not help. and my company security would not let me get the program to share the spreadsheet. at least with the macro tools one would need. but I do have images and some thoughts.

this screen shot is the page that I am pulling the summary data into. the tab name is Alaska with both capital and lower case letters. the formula in the bar at the top is the one in J1 . I noticed that in the formula the macro created, the search by item (ie the tab name) is all capitals.

when I manually changed it to mixed-case letters it worked. I am not sure what it put in there to make it not find the word Alaska. all the formatting is just general...not that it is a big deal but I did check that.

On the summary sheet you see what appears to be two sets of identical data. the first one is the previous data. the second set is the new data. it is possible people made changes to old data. I am going through and making sure that I get the previous data to match the current. this example does bc I already balanced it,

 

[Joe4]

Based on the example of your Summary Sheet, your VLOOKUP formula does not look correct.
It is trying to match on column C, and return the values from column L.
But your screen print shows numbers in column C and blanks in column L.

Are you familiar with the VLOOKUP function?
Do you know how to use it properly?

Can you manually type a VLOOKUP formula in cell J1 and get it to return the value you are expecting?
That is the first step. Before we can "recreate the formula VBA", we need to come up with a working formula so we know how to build it.