For Loop to name a sheet then use sheet name in formula

[EmyrPC]

Hey everyone,

I've created a simple For Loop thingy and one of the things it does is name a create and name a sheet:

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")

which is great, works fine. Except when I need to use the name in a Formula:

Range("Q2:Q" & Range("A2").End(xlDown).Row).Formula = "=IF(COUNTIF('AE'!C:C,SUBSTITUTE((C2&D2),"" "",""""))=1,SUBSTITUTE((C2&D2),"" "",""""),"""")"

This doesn't work. Does anyone know how I can get the formula to select the right sheet?

Any help would be much appreciated.

 

[EmyrPC]

Answered here:

https://www.mrexcel.com/forum/excel...et-using-formula-post5259831.html#post5259831

 

[Joe4]

In the future, please take care not to post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.

Thank you.