Naming a sheet and using in formula

[EmyrPC]

Hey everyone,

I've created and named a sheet based on a list in another sheet. Which works great:

Dim WB As Workbook
Set WB = Workbooks("Workbook")
Dim List As Worksheet
Set List = WB.Sheets("Sheet1")
Dim r As Integer
Dim Last_Row As Integer
Last_Row = Application.WorksheetFunction.CountA(List.Range("A:A"))
'Start Loop
For r = 2 To Last_Row

WB.Sheets.Add
WB.ActiveSheet.Name = List.Range("A" & r).Value & "AE"
Dim AES As Worksheet
Set AES = WB.Sheets(List.Range("A" & r).Value & "AE")

The problem I have is now referencing the sheet in a formula (vlookup/countif etc)

.Formula = "=VLOOKUP(BJ6,' [I][B]WHAT CAN I PUT HERE[/B][/I] '!L:AB,17,FALSE)"

Can anyone help please?

 

[footoo]

Same as this post : https://www.mrexcel.com/forum/excel...p-name-sheet-then-use-sheet-name-formula.html

 

[steve the fish]

You can assign the sheet to a variable then use that. For example:

Set sh = wb.Sheets.Add
'more code
.Formula = "=VLOOKUP(BJ6,'" & sh.Name & "'!L:AB,17,FALSE)"

 

[EmyrPC]

Thank you Steve,

Would that work with several new sheets e.g. set sh1 = , set sh2 = etc etc?

 

[steve the fish]

Just assign the variable sh to the new sheet within the loop.

 

[EmyrPC]

Thanks Steve,

I'll give it a go

 

[footoo]

Thanks Steve,

I'll give it a go

You should also post to your other thread with a reference to this thread, so that people don't waste their time replying to the other thread.