Recorded Macro Variable in concatenate

[MrZacMatthews]

I'm new to using macros and have attempted to make this one to save me some work. It is supposed to copy a sheet "Template", concatenate some data from another sheet and then use this to rename the sheet. Most of this is working as expected so I tried to add a loop and variable, which unfortunately hasn't worked.

The problem I have is that I can't repeat this and incrementally move through the list of names to concatenate as I can't put the variable "i" into the concatenate function. 

Clearly I'm not doing this correctly, can someone please recommend a solution?

Here is the vba:

Dim i As Integer
i = -7

Do While i < 30
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(3)
Range("C9:F9").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE('Student Data'!R[i]C[-1],"" "",'Student Data'!R[i]C)"
Sheets("Template (2)").Select
Sheets("Template (2)").Name = Range("C9")
Range("I1") = i
i = i + 1
Loop

[#2 · Accepted solution]

You can put the variable i in the function, but you have to build it. So a short example:

Instead of "=CONCATENATE('Student Data'!R[i]C[-1]" for the first part of your formula do this

"=CONCATENATE('Studet Data'!R[" & cstr(i) & "]C[-1]"

That will convert the variable i into a string (cstr() is convert to string) then build the function dynamically. So if i = 1 it becomes 

=CONCATENATE('Student Data'!R[1]C[-1]

Then when i = 2 it is 

=CONCATENATE('Student Data'!R[2]C[-1]

Etc.

[#3]

Thanks Ed! I really appreciate the help.