Run a MACRO Vlookup with the next sheet

[Rudy]

Hello, first time user, very ignorant in Excel, thanks for your service. This is the first MACRO I have ever recorded (did not create it in VBA, just recorded my steps). I am trying to run a daily vlookup with the next sheet over but when I run the MACRO that I recorded, it runs the vlookup against the sheet I selected when I first recorded it. This would be sheet '5.11' below:

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  ActiveCell.Select
  ActiveCell.FormulaR1C1 = "V-Balance"
  ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  ActiveCell.Select
  ActiveCell.FormulaR1C1 = "diff w next"
  ActiveCell.Offset(1, -1).Range("A1").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'5.11'!R1:R1048576,7,FALSE)"
  ActiveCell.Select
  Selection.Copy
  ActiveCell.Offset(0, -1).Range("A1").Select
  Selection.End(xlDown).Select
  ActiveCell.Offset(0, 1).Range("A1").Select
  Range(Selection, Selection.End(xlUp)).Select
  ActiveSheet.Paste

I want the vlookup to automatically run against the next sheet. So if i am running the MACRO on sheet 5.12, it should run the vlookup with sheet 5.13 and not 5.11. I have a workbook with one sheet per day, and I run the vlookup daily comparing to the data of the next sheet (next day). Thank you!

 

[Hui]

Rudy

Can you please post a sample of your file with say 4 days of worksheets
You can put any values in the data areas if it is confidential
It is the layout that is important to us

 

[Rudy]

Hui, thanks for getting back to me!
So I created a simpler replica of my file. Notice that I recorded the MACRO on the 5.11 sheet when I ran the vlookup to compare to the 5.12. So when I run the MACRO on the other sheets, it naturally compares to 5.12 but I want it to compare to the following day (next sheet) automatically.
Thanks for your help!

 

[Rudy]

Hi, can anyone help me out with this one?

 

[Marc L]

Hi !​

Sub Demo()
With ActiveSheet
    If .Index = Worksheets.Count Then Beep: Exit Sub
    AD$ = Worksheets(.Index + 1).Cells(1).CurrentRegion.Address(External:=True)

    With .Cells(1).CurrentRegion.Columns(1).Offset(, 2)
                              .Cells(1).Font.Bold = True
                                  .Cells(1).Value = "V-Balance"
        .Cells(2).Resize(.Rows.Count - 1).Formula = "=VLOOKUP(A2," & AD & ",2,FALSE)"
    End With
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

 

[Rudy]

Hi Marc,

Thanks for taking a look!! I am so ignorant with VBA code... I don't know if this works. Can you help me out by showing me where to insert this piece of code in the code I posted originally. Or tell me what part from your code is the piece that does the vlookup with the next sheet so I can try to replicate it?

Thank you and sorry for my utter ignorance.

 

[Marc L]

Just copy my procedure in a module and run it …
​

 

[Rudy]

Awesome, had to play around with it a little bit to fit it into my real file and make it work but it is working great so far. The vlookup on the real file should run on Column 7 so I just figured out where to stick the 7: .Offset(, 7)

Thank you Marc!

 

[Marc L]

You're welcome !

Should be .Offset(, 6) … (from first column + 6 = 7th column)

If the 7th column always already exists, use Columns(7) without Offset …​