Referencing to a sheet a newly created sheet in excel vba formula

[MennoVK]

Hello excel experts,

New vba rookie here.
I'm doing a project for my company and I'm creating a VBA macro for it.

This is the code

Sub CreateNewSheet()
Dim i As Byte

- Here the user creates a Name for the new sheet in which he needs to input some data.
sheet_name_to_create = InputBox("Vul project naam in", "Nieuw invul formulier")

- Then here VBA checks if the sheetname already exists.
For rep = 1 To (Worksheets.Count)
    If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
    MsgBox "Het project bestaat al"
    Exit Sub
    End If

- Here it uses a Template for the sheet newly to be created.    
Next
    Sheets("TEMPLATE").Copy After:=Sheets(1)
    
- Finnaly here it creates the New sheet
For i = 1 To 1
    Sheets(ActiveSheet.Name).Name = sheet_name_to_create
    
- Now in the next part VBA goes to my calculation sheet and moves to the first empty row.
Next i
Sheets("Calculatie").Activate
ActiveSheet.Cells(1, 1).Select
Do Until IsEmpty(activeCell) = True
    activeCell.Offset(0, 1).Select
    r = r + 1
    Loop

- Here it names the first cell of the empty row to the sheet name and moves the active cell one down.
activeCell.Value = sheet_name_to_create
activeCell.Offset(1, 0).Select

- Now here is where I want VBA to input a formula with the newly created sheet name. But here it gives an error.
activeCell.Formula = "=IFERROR(INDEX('sheet_name_to_create'!B$11:N$28,MATCH(Calculatie!B2,'sheet_name_to_create'!A$11:A$28,0),MATCH(Calculatie!A2,'sheet_name_to_create'!B$8:N$8,0)),Geen Beoordeling)"
End Sub

The formula used in the macro is needed to search in the created sheets for data which I need in my calculation sheet.
And every time a new sheet is created the data is needed to be transferred to my calculation sheet.
That's my little macro I created.
Hopefully someone can help me with my problem.

Greetings Menno.

 

[steve the fish]

You have to concatenate your variable into the strin<code>g so wherever you put sheet_name_to_create put this instead:

" & sheet_name_to_create & "

Oh and by the way you really dont need to use loops to find the last row of your data. There are countless examples on here to find last row.
</code>

 

[Dryver14]

try

"=IFERROR(INDEX('&sheet_name_to_create&'!B$11:N$28,MATCH(Calculatie!B2,'&sheet_name_to_create&'!A$11:A$28,0),MATCH(Calculatie!A2,'&sheet_name_to_create&'!B$8:N$8,0)),Geen Beoordeling)"

 

[MennoVK]

Thx with this formula:
activeCell.Formula = "=IFERROR(INDEX('" & sheet_name_to_create & "'!B$11:N$28,MATCH(Calculatie!B2,'" & sheet_name_to_create & "'!A$11:A$28,0),MATCH(Calculatie!A2,'" & sheet_name_to_create & "'!B$8:N$8,0)),Geen Beoordeling)"

it works ^^

 

[MennoVK]

My formula works almost I needs to put "Geen beoordeling" in the cell when it error's but when I enter this formula;

activeCell.Formula = "=IFERROR(INDEX('" & sheet_name_to_create & "'!B$11:N$28,MATCH(Calculatie!B2,'" & sheet_name_to_create & "'!A$11:A$28,0),MATCH(Calculatie!A2,'" & sheet_name_to_create & "'!B$8:N$8,0)),"Geen Beoordeling")"

It error's in VBA at the "Geen Beoordeling" part.
I need the quotation marks in the formula to be copied to the cell.

 

[steve the fish]

Use double quotation marks:

<code><code>""Geen Beoordeling""</code></code>