asked on

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

johnnyho_

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.

maximyshka

ASKER

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

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnnyho_

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

Open in new window

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

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.
Book4-johnnyho-.xlsm

maximyshka

ASKER

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 ]

You're very welcome.