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