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
maximyshkaAsked:
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.

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

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.
0
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
0
[ fanpages ]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

Your issues matter to us.

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

Start your 7-day free trial
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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

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
0
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.
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.