# 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
Book4.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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, "")
``````

Just copy it to cell A1 and then to the whole range of cells you need to have populated with names.
0
Author 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
0
IT Services ConsultantCommented:
Hi,

I selected cells [A3:A30] & entered the following array formula (into cell [A3]):
=IF(ROW()-2<=A\$2,A\$1,"")

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:
=COUNTA(A3:A31)
to
=COUNTA(A3:A31)-COUNTBLANK(A3:A31)+1

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

The result may be seen within the attached workbook.

BFN,

fp.
Q-28239915.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
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".

Formula
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, "")
``````

Macro
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.

S
``````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
``````

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.
Book4-johnnyho-.xlsm
0
Author Commented:
Thanks a lot.  This is an excellent job.