Excel formula to automate copying within spreadsheet

Is there an excel formula to automate copying name by n amount of times. Can this be done by copying macro from one name to another?

This is very easy to do it manually.  I have lots of names to do it.  Please see the attached spreadsheet.

So, first row is list of names; and second rows is number of times to being copies.  Last row is just excel formula to count names to avoid careless errors
Who is Participating?
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:

I selected cells [A3:A30] & entered the following array formula (into cell [A3]):

That is, I used [CTRL]+[SHIFT]+[ENTER], rather than simply [ENTER] to confirm the formula entry.

I then copied cell [A3], across row 3 to cell [G3].

In cell [A32] I updated the existing formula from:

I then copied cell [A32] to cells [B32:G32].

The result may be seen within the attached workbook.


Hi maximyshka,

I would rather use the VBA to do this exercise.

However the possible formula you may go for is:

=IF((ROW(A3) - 2) <= A$2, A$1, "")

Open in new window

Just copy it to cell A1 and then to the whole range of cells you need to have populated with names.
maximyshkaAuthor Commented:
Thanks.  However, it's not working for the task I need.

I need to copy names by number of times designated in the spreadsheet.

Your formula will only allow to copy names.  This is not what I asked in the question
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hi maximyshka,

hmm, ok. Do not get me wrong but I am a bit confused as your goal is still unclear to me.

However I took a look at your demand once again.

Attached you can find a workbook with two sheets "Formula" and "Macro".

Contains just the formula mentioned in my previous comment copied to several cells. Please check this out.

=IF((ROW(A3) - 2) <= A$2, A$1, "")

Open in new window

Population of cells with names is done using the button "Copy names". The macro first cleans the content of all rows starting with row 3 and then populates cells in each column with names.

ub CopyNames()

Dim x As Long

Rows(3 & ":" & ActiveSheet.Rows.Count).Delete

For x = 1 To Cells(1, 1).End(xlToRight).Column

    Range(Cells(3, x), Cells(Cells(2, x) + 2, x)).Value = Cells(1, x)

Next x

End Sub

Open in new window

FYI, the population of the fields could be changed in the way so that it is done upon a definiton/change of the value in second row.
maximyshkaAuthor Commented:
Thanks a lot.  This is an excellent job.

FP: your approach is working.

johnnyho: you also did a very good job.

However, I'll choose FP approach: very easy and simple to use.
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.