Macro with vlookup with variable sheet

[stuck4once]

Hi,

I am trying to write a loop macro that will insert a vlookup that references
the previous worksheet/tab in a work book.

The worksheet/tab needs to be variable as the Macro will be run in a new
worksheet/tab each month and the vlookup will need to reference the previous
sheet only.

e.g

Col A. Col B. Col C.
ref Notes Previous Notes
1 Ok to Process
2 In query
3 Send back

So, if the above is set out in sheets 1, 2 and 3 in reference to 1=Jan 08,
2=Feb 08 and 3=March 08.

When I run the macro in Feb 08 I want the vlookup to reference sheet jan 08,
but when I run the macro in Sheet March 08 I want it to reference Sheet Feb
08 instead of Jan 08

in column c the macro will insert "vlookup(a1,"previous sheet"!A:B,2,false)

Any help will be greatly appreciated

 

[Lazzzx]

Hi
Try the following line:
Range("C2:C4").FormulaR1C1 = "=HLOOKUP(RC[-2]," & ActiveSheet.Previous.Name
& "!RC[-2]:R[2]C[-1],2,FALSE)"

rgds,
Lazzzx

 

[Joel]

You need name all you worksheet names consistant ly. Either spell the
month names out or have them all 3 letters. The month of March is spelled
out while JAN is a abbreviation. I assumed all Month names where three
letters. A single quote is needed around a sheet name the has a space in it.

Make sure you testt for the month of January because the function below will
return DEC for the worksheet that has JAN on it

This code takes the month from the DATE function
LastMonth = "'" & Format(Month(Date - 1), "mmm") & " 08" & "'"
ActiveSheet.Range("C10").Formula = "=VLOOKUP(A1," & LastMonth &
"!A:B,2,FALSE)"

This function takes the month from the worksheet name
MonthNm = Left(ActiveSheet.Name, 3)
MonthNumber = Month((DateValue(MonthNm & " 1 08")))
LastMonthDate = DateSerial(8, MonthNumber - 1, 1)
LastMonthSheet = "'" & Format(LastMonthDate, "mmm") & " 08" & "'"
ActiveSheet.Range("C10").Formula = "=VLOOKUP(A1," & LastMonthSheet & _
"!A:B,2,FALSE)"

 

[Lazzzx]

Sorry, I pasted the wrong formula into my answer:
Range("C2:C10").FormulaR1C1 = "=VLOOKUP(RC[-2]," & ActiveSheet.Previous.Name
& "!C[-2]:C[-1],2,FALSE)"

Lazzzx

Hi
Try the following line:
Range("C2:C4").FormulaR1C1 = "=HLOOKUP(RC[-2]," &
ActiveSheet.Previous.Name & "!RC[-2]:R[2]C[-1],2,FALSE)"

rgds,
Lazzzx

Hi,

I am trying to write a loop macro that will insert a vlookup that
references
the previous worksheet/tab in a work book.

The worksheet/tab needs to be variable as the Macro will be run in a new
worksheet/tab each month and the vlookup will need to reference the
previous
sheet only.

e.g

Col A. Col B. Col C.
ref Notes Previous Notes
1 Ok to Process
2 In query
3 Send back

So, if the above is set out in sheets 1, 2 and 3 in reference to 1=Jan
08,
2=Feb 08 and 3=March 08.

When I run the macro in Feb 08 I want the vlookup to reference sheet jan
08,
but when I run the macro in Sheet March 08 I want it to reference Sheet
Feb
08 instead of Jan 08

in column c the macro will insert "vlookup(a1,"previous
sheet"!A:B,2,false)

Any help will be greatly appreciated

 

[stuck4once]

Hi Joel,

Thanks for you help on this, it worked great, until I put it in to a macro
that I already have running.

Now it comes up with:

a compile error and highlights the word format in the lastMonthSheet line of
the macro.

Any idea why?

 

[stuck4once]

Joel,

thanks for this, it all works fine, except when I add it to an already
existing macro.

It keep coming up with a compile error and highlighting the word format in
the LastMonthSheet line of the macro.

Any ideas why??