Previous Topic Next Topic

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

Closed
TheAssistant Posts 2 Registration date Wednesday April 17, 2013 Status Member Last seen April 18, 2013 - Apr 17, 2013 at 05:03 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 20, 2013 at 07:17 AM
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
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 18, 2013 at 03:03 AM
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.
0
Answer 2 / 4
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 18, 2013 at 05:10 AM
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
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Apr 18, 2013 at 11:34 AM
@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?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 20, 2013 at 07:17 AM
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 :)
0
Answer 3 / 4
TheAssistant Posts 2 Registration date Wednesday April 17, 2013 Status Member Last seen April 18, 2013
Apr 18, 2013 at 12:26 PM
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.
0
Answer 4 / 4
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 18, 2013 at 10:30 PM
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
0