Recorded Macro Variable in concatenate

New Contributor

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").Copy After:=Sheets(3)
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

2 Replies

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]



Thanks Ed! I really appreciate the help.

Related Conversations
Excel Table Size in Macro
WayneEK in Excel on
6 Replies
Macro issue with email connectivity
unhappyuser in Excel on
21 Replies
New Spill function making concatenate output difficult?
nickcicc in Excel on
6 Replies
Marco to create .CSV
DooDahMan in Excel on
0 Replies
vlookup using concatenate function and helper column
Angela McGhin in Excel on
4 Replies
Simple Text Searching Macro
docbrain in Excel on
0 Replies