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

MennoVK

New Member
Joined
Mar 15, 2018
Messages
5
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
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
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>
 
Last edited:

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
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

New Member
Joined
Mar 15, 2018
Messages
5
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

New Member
Joined
Mar 15, 2018
Messages
5
My formula works almost I needs to put "Geen beoordeling" in the cell when it error's but when I enter this formula;
Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
Use double quotation marks:

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

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,404
Members
409,871
Latest member
i1patrick
Top