VLOOKUP VBA looping multiple worksheets

[financehelp_excel]

Thanks in advance for solving this.

I've created a macro but it's returning an error. I have a two column list in my first sheet and I want VLOOKUP to go down the list. So for cell C1 sheet 2, I want it to lookup Cell A2 in sheet named "First". For cell C1 in sheet 3, I want it to lookup Cell A3 in sheet named "First".

Here's what I have so far:

'Sub test()
Dim First As Integer, Last As Integer, i As Integer
First = Sheets("First").Index
Last = Sheets("Last").Index
For i = First + 1 To Last - 1
With Sheets(i)
'Range("C1").Select
'Range("C1") = Application.WorksheetFunction.VLookup(Sheets("First").Range("A1+1"), Sheets("First").Range("$A$1:$B504"), 2, False)
End With
Next i
End Sub

 

[Worf]

Please test this:

Sub test()
Dim i%, f As Worksheet
Set f = Sheets("First")
For i = Sheets("First").Index + 1 To Sheets("Last").Index - 1
    Sheets(i).Range("C1") = Application.WorksheetFunction.VLookup _
    (f.Range("A" & i), f.Range("$A$1:$B504"), 2, False)
Next
End Sub

 

[financehelp_excel]

Thank you so much! You are awesome!!!

One quick question, is there a way to name each tab the contents of what is in each C1?

 

[Worf]

Like this:

Sub Renaming()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    If Len(sh.Range("c1")) > 0 Then sh.Name = sh.Range("c1")
Next
End Sub

 

[financehelp_excel]

Brilliant!!! You are my savior! Thank you!!

 

[Worf]

You are welcome