Copy paste data into new sheet, with conditions

[mad4max]

Hi guys !

I need your help to create an export file of datas that are in sheet.

Here is the situation:

I have a sheet called: “member” containing all information about the member:

• First, Last Name;
• Address;
• Year of billing with 6 columns containing:
  [*=1]Invoice Number;
  [*=1]Invoice Date;
  [*=1]Starting date of contract;
  [*=1]Ending date of contract;
  [*=1]Amount Base contract;
  [*=1]Amount Options;
  [*=1]Corrections or discounts on contract;
  [*=1]Total Amount of Contract

​

Now, I need to create a macro that will copy/paste the values of some column, over 3 rows, because to create an invoice on our billing’s system, the import procedure ask me to put on a single line the information that I have actually in column… Concrete, it should look as it in my destination’s sheet.

The problem is that what I had in Sheet “Member” in several columns, now should be in rows… I need always 3 rows for each invoice. The problem is to copy/paste (as value) the data of the columns, into these rows, knowing that I have around 1000 lines that are increasing every day…

I would like to have a macro that could pick up data in a Sheet called “Member” and put it in sheet called “WB_INV_Export, respecting the format I need.

Of course, it can happen that some lines could be empty as in fact, I have 5 serial of columns… 2011.. 2012.. 2013.. 2014.. and in some cases, it could be happen that some data are in the year 2013 and 2015 only, as the member didn’t have any contract in 2011, 2012 and 2014.

In that case, there should be a test into the column containing the Invoice #. If there is no number, then the line should be skipped…

If somebody could help me with this complex task, I would be very thankfully…

I have two snapshot as example to show how data are, but unfortunately, I don't know how to show a .jpg file here or attach it... If somebody can help me, I'll add it...

Thank you for your valuable help.

 

[onlyadrafter]

Hello,

You can download some software from here at MrExcel to display your sheet in your post. Alternatively, you could just type out a before and after example in a post.

 

[mad4max]

Hi guys...

Here is a link on where you can download a file example I created and on which I added some explanations.

Click on the following link: Document Cjoint

The file name is called: EFkf42o6UUK_WB-Export-E.xlsx

Thank you for your valuable help!