Need vba help..Pls... :(

[xcelbign]

Hello All,

I am new member here and this is my very first request asking your help...
I am looking to performa vlookup in VBA.

An excel workbook which is a monthly report and each sheet is named as dates ("DD"). Everyday I have to perform a vlookup to retrieve the comments from yesterday sheet. I am trying to perform this through VBA.. I have tried in all possible ways.. however I am not getting as each sheet is named as dates..

Can anybody please be so kind to help me with the VBA code...This will be of great help and your help is much appreciated...

 

[Stoneboysteve]

xcelbign,

Each day you get a new sheet and you add it to your workbook, or are you using vlookup from one workbook to another?

Stoneboysteve

 

[xcelbign]

Thanks for your reply...

Each day I add a sheet to my workbook. For example today i will add a sheet to my workbook and rename the sheet as todays date. Then I will do vlookup in todays sheet with yesterday sheet to get comments which are in yesterday sheet... This vlook up is within a workbook...

 

[robert3708]

aaa

 

[xcelbign]

Hello All,

Can anyone pls be so kind to help me in this problem...
I have written the code.. however I am getting #Value error because double quotes sign is getting added in vlookup formula...

Below is the code i have used.... Can anyone pls help me to remove those quotes or with codes is highly appreciated...


Sub Macro1()
fname1 = "'" & Format(Now, "DD") - 1 & "'" & "!$A:$B"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],""" & fname1 & """,2,0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B9"), Type:=xlFillDefault
Range("B2:B9").Select
Columns("B:B").EntireColumn.AutoFit
Range("B2").Select
End Sub


Awaiting for your reply..
Xcelbign