Avatar of Vrinda Vasudeva
Vrinda Vasudeva
 asked on

VB Macro to add 17 data rows (on different sheet) for every 1 manual row added (on a different sheet)

Hi all,
Please I would really appreciate your help here.
I have attached the sample excel file with sample 17 rows added (on 'Add Rows' tab) for 1st row in tab 'Source Data'. I need a macro that basically inserts 17 data rows on tab:'Add Rows' based on every 1 data row added on tab: 'Source Data'. Header Data remains same on tab 'Add Rows'.

For the 17 rows added on tab 'Add Rows' I need below values:
(1) Column 'Item Number': Same value as B2 (tab Source Data) appearing 17 times.
(2) Column Attribute Name: Same values as example provided (from Parent ID to Price Quantity)
(3) Column Attribute Value:
- C6 (Extended Price): $ sign + same value as H2 from 'Source Data' + AUD
- C7 (Item Category): Standard (remains same does not change)
- C8 (Material Group): relevant value from C2:C15 (tab: Material Groups) depending upon M2 in tab Source Data.
- C14 (Supplier Part ID): same as F2 from tab 'Source Data'.
(3) Column Display Text: Same values as example provided (from Parent ID to Price Quantity)
(4) Column Type: Same values as example provided (from Text (single line limited) to Whole Number)
(5) Column Description: Same value as B2 (tab Source Data) appearing 17 times.
(6) Column Is Term added from Item Master: 'No' appearing 17 times
(7) Column Formula: Blanks 17 times

Many thanks!
Expert-Exchange_Template.xlsx
VB ScriptMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Welcome to Experts-Exchange Vrinda. Do you want a total of 17 similar rows or do you want to add 17 rows for a total of 18?
Martin Liss

Will the Item Numbers on the Add Rows tab always be consistent? To ask in another way, could column 'A' have 1501265275 and  1501265276 (or other values) at the same time?
Vrinda Vasudeva

ASKER
Thanks Martin.
Column 'A' in tab 'Add Rows' will always have the same value as the corresponding cell value in column B (tab: Source Data).
For e.g. the 17 rows in tab 'Add Rows' have the same value for Item Number as the cell B2 from tab Source Data.
For any subsequent rows added in tab 'Source Data', relevant cell value from column B appears as Item number (for 17 new rows added) in tab 'Add Rows'.
Please let me know if is clear.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Martin Liss

Sorry but it's not. In the workbook you attached, all the item numbers on the Add Rows sheet are the same. Will it always be that way?

Also you didn't answer my first question.
Vrinda Vasudeva

ASKER
Regarding the first question, Martin.
I have about 1000 rows that I will need to manually add in tab 'Source Data' with different cell values (as provided in example template).
For each 1 of those rows, I need 17 rows added in tab 'Add Rows' (as provided in example template).
Columns in tab 'Add Rows' that will appear as is i.e. no change in values: Attribute Name, Display Text, Is Term added from Item Master, Formula.
- Column Item Number in tab 'Add Rows'- depends on column B value in tab 'Source Data' for e.g. in provided template, Item number '1501265275' appears 17 times because the user entered '1501265275' in B2 (tab 'Source Data).
- Column Attribute Value in tab 'Add Rows': For example in template provided
    (1) C6 = depends on column 'H' value in tab Source Data
    (2) C7 = value 'Standard' repeated 17 times
    (3) C8 = Relevant Material Group Name (from tab 'Material Groups') for column M value in tab Source Data for e.g. in provided template,
    user entered '99000102', so the Material Group Name will be '99000102 PPP - Graffiti Remov' as per the value in tab ' Material Groups'.
    (4) C14 = depends on column 'F' value in tab Source Data.

- Column Description in tab 'Add Rows'- depends on column C value in tab 'Source Data' for e.g. in provided template, Description 'PPP - Graffiti
  Removal - G1.1' appears 17 times because the user entered 'PPP - Graffiti Removal - G1.1
 1.1' in C2 (tab 'Source Data).
Vrinda Vasudeva

ASKER
Yes you are right, it is the same for the example I provided in excel file.
As an example, I have uploaded an excel file in this post. And I have shown how the next 17 rows will look like.
The item number will be repeating 17 times for the value user entered in cell B3 tab (Source Data).
Expert-Exchange_Template.xlsx
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

I was hoping for Yes or No answers, but maybe I'm making this more complicated than it needs to be.

Let me know if this is a true statement.

You currently show 17 rows. Do you want that set of 17 rows duplicated 16 more times so that it looks like this? (the blank rows were added for the purpose of this picture)2020-03-01_19-04-35.png
Vrinda Vasudeva

ASKER
No, Martin. That is not what I want.
In tab 'Source Data', eventually I will actually have 1,000 different rows (I only showed two as an example).
For every single row out of the 1,000 rows on tab 'Source Data', I want 17 rows added on tab 'Add Rows'.
So, in total the tab 'Add Rows' will have 17,000 rows.

I am attaching a file that has 265 sample rows (this is how the data will be put in on tab 'Source Data').
I do not yet have the full data for 1,000 records was just hoping if the macro could consider the max cap of 1,000 records on tab 'Source Data').

To put this in perspective, each time a set of 17 rows repeat in tab 'Add Rows', I want the item number copied from Column B values in 'Source Data'.
So, for the 1st set of 17 rows that repeat on tab Add Rows:
- Item Number = B2 (from tab Source Data)
- C6 (tab 'Add Rows') = H2 (from tab Source Data)
- C7 (tab 'Add Rows') = value 'Standard'
- C8 (tab 'Add Rows') = matched Material Group value (from tab: Material Groups) based on M2 value (from tab 'Source Data).
- C14 (tab 'Add Rows') = F2 (from tab 'Source Data)

The second set of 17 rows that repeat in tab 'Add Rows')
- Item Number = B3 (from tab Source Data)
- C23 (tab 'Add Rows') = H3 (from tab Source Data)
- C24 (tab 'Add Rows') = value 'Standard'
- C25 (tab 'Add Rows') = matched Material Group value (from tab: Material Groups) based on M3 value (from tab 'Source Data).
- C31 (tab 'Add Rows') = F3 (from tab 'Source Data)

The third set of 17 rows that repeat in tab 'Add Rows')
- Item Number = B4 (from tab Source Data)
- C40 (tab 'Add Rows') = H4 (from tab Source Data)
- C41 (tab 'Add Rows') = value 'Standard'
- C42 (tab 'Add Rows') = matched Material Group value (from tab: Material Groups) based on M4 value (from tab 'Source Data).
- C48 (tab 'Add Rows') = F4 (from tab 'Source Data)

so the cell values referenced in tab Source Data will increment (for all 1,000 rows in tab 'Source Data).

Columns 'B', 'D', and 'E' are the same way as you show.
Expert-Exchange_Template.xlsx
Martin Liss

Thanks, I understand now. I'll complete this tomorrow.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Vrinda Vasudeva

ASKER
Thank you very much Martin- much appreciated.
Martin Liss

Click the "Add Rows" button on the Source Data tab. The code as written can handle more than 1000 rows and is limited only by the maximum number of rows your version of Excel allows / 17.
29174373.xlsm
Martin Liss

I should have mentioned that I added an hidden sheet called "Template". It contains the "Attribute Name", "Display Text" and "Type" data for the 17-row sets. In the code, each column is referred to by way of a named range that represents the contents of the column starting at row 2. The named range for the "Attribute Name" column is called "Attribute_Name" and the other two are similarly named.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vrinda Vasudeva

ASKER
Perfect thank you so much Martin. Great work! Very much appreciated.
I have just noticed two things- if you could please look into this.
As per the image below-
1) Adding $ sign for C6 (and for all $ values appearing in the batches of 17 rows at relative positions).
2) Value G1.1 should appear in C14 (currently it appears at C15). And subsequently for all batches of 17 rows at relative positions.
I cannot thank you enough for this if you could please update the code for above 2 changes.
It looks awesome.
Thanks Yash
Pic.png
Martin Liss

Vrinda Vasudeva

ASKER
Great thank you heaps Martin :) It is awesome.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vrinda Vasudeva

ASKER
Thank you very much Martin for your help.
Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018