Previous Topic Next Topic

Excel Macro to insert the same 120 rows between 1000 unique rows

Closed
Posts
2
Registration date
Wednesday April 17, 2013
Status
Member
Last seen
April 18, 2013
 -
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,

I'm creating a product catalog database in Excel. I have 1000 unique product lines (rows). Each of those 1000 products is offered in 120 colors -- all of those colors are the same for each product. So I need to insert the same 120 color name lines (rows) - (which are in column H if that is relevant to you) - in between each of the 1000 product lines. As it is now, I'm Copying the 120 rows and Inserting between each of the 1000 rows - at this rate it'll take me hours. After this first 1000 products, I have another 4 sets of 1000 that will have their own separate 50-100 colors. I need a macro to do it - I've started watching Lynda.com tutorials, but I'm a basic/intermediate Excel user and have never done a macro before. I'm Excel literate, so if you just explain it just as you would do it - I'll get it! Thanks so much for any assistance you can offer.

4 replies

Answer 1 / 4
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 63
Hi TheAssistant,

From your post it is little difficult to visualize as to how the rows containing the product name and colors are organized. It is suggested that you please upload the sample file to the following website:

https://authentification.site

Note: Please do not upload any files that contain any kind of personal or confidential data in this open forum.

Please do write back to us.
Answer 2 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
There seems to be some thing inherently wrong, if one has to copy same rows again and again and more so if those rows are same. So IMHO, either excel layout is not correct or excel is not the right tool for this
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
 491
@Rizvisa
Doesn't that depend on the excel version.
1000*120+1000
+
4*(1000*100+1000)
=525000 rows

That is beyond the limit of excel 2003 (65536) but not 2010 (1048576).

Right? Or did you meant something else?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Hi Trowa. Well i must confess that I did not even think of max rows.
What came to my mind was that it sound like 1 row of product followed by 120 rows for color. Since 120 rows are being repeated, I was thinking more on a line of why not have 120 rows as columns (if possible). Or may be use some other tool (Access ?) to do what is needed. Of course, all depends on requirement. Forgot to thank you about exposing "ROUNDUP" functionality to me. thanks for that :)
Answer 3 / 4
Posts
2
Registration date
Wednesday April 17, 2013
Status
Member
Last seen
April 18, 2013
So I spent a few hours with a programmer on JustAnswer.com and learned that it's a VBA code I needed.
Nothing inherently wrong with it. Excel is used to import product data into our FileMakerPro based software. The this particular company is a custom tile co. They have a core 1000 products, offered in 4 different glazes (gloss, matte, crackle & old world) and there are 120 different color options available for gloss, a different 105 colors for matte, 65 for crackle, 40 for old world.

The view (visual) is 1 row of all product info in their respective columns, manufacturer, vendors, material, length, width, cost, etc. and then all color names and numbers each get their own row below that main product row. So that 1 product has 121 rows (for gloss anyway).
I thought that the repetitive: copy 120 rows, insert rows beneath each product row action, would use a macro.
Answer 4 / 4
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 63
Hi TheAssistant,

You are correct; I had a similar problem where I had to transpose every 4 rows to the consecutive four columns for 6644 rows. For example:

Column A
1619
Lippizan
KMM
RACKS

Transpose to:

Column B Column C Column D Column E
1619 Lippizan KMM RACKS

For this I used the following macro:

ActiveCell.Offset(0, -1).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveCell.Offset(1, -1).Select
Selection.Copy
ActiveCell.Offset(-1, 2).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveCell.Offset(2, -2).Select
Selection.Copy
ActiveCell.Offset(-2, 3).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveCell.Offset(3, -3).Select
Selection.Copy
ActiveCell.Offset(-3, 4).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveCell.Offset(4, -3).Select

However, I couldn't understand the description of the sheet so I requested for a sample sheet. I am glad to know that you have found the fix. If you feel this will help other users, please feel free to post the macro code here.

Please feel free to contact us if you need any further help.

Thanks & Regards
Zohaib R
#iworkfordell
Ask a question