We help IT Professionals succeed at work.

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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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).

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks, I understand now. I'll complete this tomorrow.

Author

Commented:
Thank you very much Martin- much appreciated.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:

Author

Commented:
Great thank you heaps Martin :) It is awesome.
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Since this is your first question, let me point you to an article that explains how to close a question/

Author

Commented:
Thank you very much Martin for your help.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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