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


"Lazzzx" skrev i meddelelsen
...
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

"stuck4once" skrev i meddelelsen
...
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