Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new cells

**blue_clouds_mountain** · 02-09-2019, 08:24 AM

Hi!
I don't know much about VPA. That is for sure now!
I have an Excel table with 4 columns (b7:E11): headers: b7="box", c7="color", d7="type", e7="date".
In range (b3:f3), I have some data that I wish to copy into X number of newly automatically inserted rows in my table. X value (or the number of rows that I wish to insert) is in cell A1.
I wish to do this by a button click automatically. However, I can't figure out the macro that I have to assign to this button.
I have been struggling with that for a very long time now. I am desperate and wonder is this something achievable in Excel? May you please help me with that?
I highly appreciate it!

Thanks

**KOKOSEK** · 02-09-2019, 08:33 AM

Can you attached sample file, please?
It could be really helpful to understand you request.

**blue_clouds_mountain** · 02-09-2019, 09:09 AM

Thanks a looooot you for your reply.

I uploaded the file

**KOKOSEK** · 02-09-2019, 10:01 AM

Connect this to your button:

Please Login or Register  to view this content.

**blue_clouds_mountain** · 02-09-2019, 10:14 AM

Thanks for your reply.

I tried. It's only working for the first entry in column B and inserts only 1 row per clicks. Have I applied it correctly?!
Is there a way to insert a specified number of rows per click?

**Fluff13** · 02-09-2019, 10:49 AM

How about

Please Login or Register  to view this content.

**blue_clouds_mountain** · 02-09-2019, 10:56 AM

It's perfect! thanks soooooooooooooooo much!
May you please explain this formula for me? so I know how to manipulate it a bit?
For example, if I wanted to copy only one entry in cell B3 but not the others C3:E3 to the inserted rows, how can I do that?
Moreover, is there a possibility to insert the rows anywhere in the table, not only below the last row?
I can't express my gratitude enough! thank you all!

**Fluff13** · 02-09-2019, 11:08 AM

How would you determine where to insert the new rows?

**blue_clouds_mountain** · 02-09-2019, 11:12 AM

below the active cell

**Fluff13** · 02-09-2019, 11:17 AM

Try

Please Login or Register  to view this content.

**blue_clouds_mountain** · 02-09-2019, 11:24 AM

Tried. it inserts the rows anywhere in the table. However, it did not copy the values anymore.
Is there a possibility that I can copy only 2 single entries in cells B3 (box) and E3 (date) to the corresponding columns in the inserted rows, but not the other entries C3 & D3 (COLOR & TYPE)?

**Fluff13** · 02-09-2019, 11:36 AM

It works for me with your test file.

Can you please decide on what EXACTLY you want, rather than continually "moving the goalposts"

**blue_clouds_mountain** · 02-09-2019, 12:02 PM

Thank you for your reply. And sorry for changing goalposts. I will edit my the title of my post so everyone can expect what is in this thread.
The last code did not work for me. It is only inserts rows with the number I specified in the cell A3, however, the inserted rows are blank. I am just learning and I wish to copy specific data in the inserted rows. The copy source is B3:E3. I want to copy these values so each value, B3 for exemple, can be copied into the column D4 in my inserted rows, or any other column that I specify.
So, it just inserting a specified number of rows based on a cell, and insert data into each one of these rows based on cells as well.

**Fluff13** · 02-09-2019, 12:17 PM

So how do you determine which cell to copy & what column/row it should go in?

**blue_clouds_mountain** · 02-09-2019, 12:22 PM

Thank a lot Fluff13 for your help. I greatly appreciated.
I determine it based on the headers of my table. so, for example, the value on cell B3 goes to the column which is called box, and e3 goes to the column called color... etc

**Fluff13** · 02-09-2019, 12:26 PM

That's what the code in post#10 does.
Select any cell in the table & run that code.
It will insert new rows below the active cell & insert the contents from B3:E3 into those rows

**blue_clouds_mountain** · 02-09-2019, 12:36 PM

Yes, it does. It looks I did a mistake before even I tried it several times! Anyway, it works for me now! Thanks a lot!

**Fluff13** · 02-09-2019, 12:40 PM

You're welcome & thanks for the feedback

**KOKOSEK** · 02-09-2019, 12:47 PM

Originally Posted by blue_clouds_mountain

Thanks for your reply.

I tried. It's only working for the first entry in column B and inserts only 1 row per clicks. Have I applied it correctly?!
Is there a way to insert a specified number of rows per click?

Sorry I've missed that You want to populate it X times.

Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new cells

LinkBack

Thread Tools

Rate This Thread

Display

Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new cells

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Re: Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new c

Thread Information

Users Browsing this Thread

Similar Threads

Insert set number of rows between data and copy and paste data above empty rows

Copy cells with data - paste (insert rows) to next sheet.

how to have table insert formulas into cells when adding rows by adding data

[SOLVED] Insert Multiple Rows Based Off Number in Cell and Copy Data From Above New Rows

Multi functional macro- insert rows, copy data- but never sure how many rows it will

Copy paste data, insert rows, insert data and change . to -

using a dynamic reference cell to copy a range of cells within a data table

Bookmarks

Bookmarks

Posting Permissions