Excel - macro

I try to record a macro, but have run into a problem:  my macro
includes vlookup a pivot table.  But the pivot table sheet did not
always come out the same name( in my macro, it is called " sheet2"),
so the vlooup does not know where to look, and returns error.    Does
someone know how to solve this problem?   Thanks a lot!

Hi Cindy,
Provide the code of the macro you are recording, you can find it in the vba=
ide by typing ALT+F11 and look for the workbook where you have created the=
macro in the left window and there you should find one or more macro in th=
e module folder. Copy the code where you want to customize the code for you=
r vlookup. Then someone here or me should be able to help.
Pascal Baro

Hi Cindy,

I have received your code from the macro you have recorded.
I cannot tell much myself but first to define a name for your pivot table. T=
his can be done in Excel in the formula tab "define name" then check in "Na=
me manage" if you run 2007 or 2010 or the insert menu I think if you run 20=
03,tough it is still called up with "define name" or "name manager".

Then in your code, replace the field of the worksheet you want to query in =
the vlookup function with the name you have defined. I can see in your code=

ActiveCell.FormulaR1C1 =3D "=3DVLOOKUP(Sheet1!RC[-2],'navision '!R2C1:R800=
0C13,12,FALSE)"

ActiveCell.FormulaR1C1 =3D "=3DVLOOKUP(RC[-13],Sheet1!R5C1:R3000C3,2,FALSE=
)"
=20
The reason your code does not find the vlookup value is when you record it, =
you change worksheets, at one point you apply vlookup onto another workshee=
t (navision) and later on from this worksheet, you apply vlookup. Moreover,=
I do not know if you have ticked relative reference when you have created the m=
acro but if it is the case, if you are moving while recording your macro, run=
ning your macro can be different... And last thing which makes things not e=
asy, your using column row notation, this is a good thing if you want to ge=
t into coding but at many levels "A1, B2..." notation is more simple.

So for now, I can just tell you to define a name for your pivot table and u=
se that same name in the vlookup:      "=3DVLOOKUP(Sheet1!RC[-2],NEWNAME,12=
,FALSE)" position yourself after the "[-2]," coma and press F3, it will bri=
ng the name you have defined automatically.

Apply this to the other vlookup formula and it should be working.

HTH,
Pascal Baro

This can be done in Excel in the formula tab "define name" then check in "=
Name manage" if you run 2007 or 2010 or the insert menu I think if you run =
2003,tough it is still called up with "define name" or "name manager".
n the vlookup function with the name you have defined. I can see in your co=
de :
R8000C13,12,FALSE)"
ALSE)"
, you change worksheets, at one point you apply vlookup onto another worksh=
eet (navision) and later on from this worksheet, you apply vlookup. Moreove=
r, I do not know if you have ticked relative reference when you have created the=
macro but if it is the case, if you are moving while recording your macro, r=
unning your macro can be different... And last thing which makes things not=
easy, your using column row notation, this is a good thing if you want to =
get into coding but at many levels "A1, B2..." notation is more simple.
use that same name in the vlookup: =A0 =A0 =A0"=3DVLOOKUP(Sheet1!RC[-2],NE=
WNAME,12,FALSE)" position yourself after the "[-2]," coma and press F3, it =
will bring the name you have defined automatically.
es
text -

How could I define the name of the pivot table before it was
created?   Is there a way just to control the name of the pivot table
using (instead of using sheet 1, sheet 2 and sheet3 randomly, just
keep using sheet1?   Thanks,

e. This can be done in Excel in the formula tab "define name" then check in=
n 2003,tough it is still called up with "define name" or "name manager".
in the vlookup function with the name you have defined. I can see in your =
code :
1:R8000C13,12,FALSE)"
,FALSE)"
it, you change worksheets, at one point you apply vlookup onto another work=
sheet (navision) and later on from this worksheet, you apply vlookup. Moreo=
ver, I do not know if you have ticked relative reference when you have created t=
he macro but if it is the case, if you are moving while recording your macro,=
running your macro can be different... And last thing which makes things n=
ot easy, your using column row notation, this is a good thing if you want t=
o get into coding but at many levels "A1, B2..." notation is more simple.
nd use that same name in the vlookup: =A0 =A0 =A0"=3DVLOOKUP(Sheet1!RC[-2],=
NEWNAME,12,FALSE)" position yourself after the "[-2]," coma and press F3, i=
t will bring the name you have defined automatically.
Does
d text -

Also could you tell me what do you mean by column row notation?   I
recorded the Marco, so I do not know what you mean by that.  Could you
copy the script here if you do not mind?   Thanks,