Macro to add additional rows for orders paid in Multiple currencies

[bearcub]

I need to develop a macro that is currently beyond me. Here is the scenario.

The company will sometimes book an order that will be paid in different currencies (e.g. USD, GBP, EUR, JPY, AUD, etc). We add a commission rate for each currency we're going to pay the rep on.

On a line we might have 5% to be paid to the US person, 3% paid to the UK person, etc.

What I would like to do is insert a new row, copy all the data from the source row, highlight the source row with the duplicate rows in one color and leave only one rate per line.

I would like to create a macro that will look at each cell on each row in the specified range and insert a new row for each line that contains more than one currency %.

I'm attaching a picture of situation where the initial report had one line but I added an extra line because their were to payments being paid in GBP and USD. In some cases, I might 4 or more people being paid in different parts of the world. If there were 4 people, I would need to add 3 copied rows with the one currency % per row. Does this make sense?

Here is an example:

Before					
Quote Line  	US (USD)	  UK (GBP)    Sub	US (USD)	UK (GBP)
QL-1186755        0.13%  	   7.41%       UK        147.52 	 8,520.19 
					
After					
Quote Line  	US (USD)	  UK (GBP)    Sub	US (USD)	UK (GBP)
QL-1186755		           7.41%       UK	      -   	 8,520.19 
QL-1186755        0.13%		               US	 147.52 	    -

Thank you for your help,

Michael

 

[Macropod]

This thread is a duplicate of: https://www.mrexcel.com/forum/excel...nal-rows-orders-paid-multiple-currencies.html
Kindly read Mr Excel's policy in Rule 12 against asking questions about the same topic in multiple threads: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
Thread closed. You may continue the discussion in your other thread.