Write multiple lines from one line

[asm19]

I am using a macro to copy rows from one sheet to another, but I want more than that.
On the sheet "PROGRAMA COBRE" is the No. Spools is greater than 1, I want you to write that line (ID, No. Spools = 1 and Packing Unit) that number of times.

example:

On the second line have:

ID	Nº Spools	Packing Unit
B0007X02480CU-S	10	120000

I want you to write this line 10 times, where the number of spools happens to 1 in all of them:

ID	Nº Spools	Packing Unit
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000
B0007X02480CU-S	1	120000

Please find attached my file so they can see what I've done.

http://speedy.sh/swzPR/test1.xlsx


Thanks

[JBeaucaire]

I have no interest in an a 9mb sample file. Please attach a sample file here to this thread.

Make sure there is just enough data to demonstrate your need, perhaps 5-10 sample starting rows. Include a BEFORE sheet and an AFTER sheet in the workbook to show the results to automate.

Remember to desensitize the data if necessary.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jolivanes]

Try this
Change Sheet references as required

Please Login or Register  to view this content.

[asm19]

I have no interest in an a 9mb sample file. Please attach a sample file here to this thread.

Make sure there is just enough data to demonstrate your need, perhaps 5-10 sample starting rows. Include a BEFORE sheet and an AFTER sheet in the workbook to show the results to automate.

Remember to desensitize the data if necessary.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thanks for reply. Now only has 5 lines, but continues with almost 9MB.
Right now I can not upload through the paperclip, sorry.


EDIT:
Upload file:

Livro2.xlsm

[asm19]

Try this
Change Sheet references as required

Please Login or Register  to view this content.

Thanks for reply. Yes, its works!

Please Login or Register  to view this content.

Thank you.

[asm19]

Try this
Change Sheet references as required

Please Login or Register  to view this content.

One more question, this time I am inserting in new sheet (column A:C), but how can I set to insert in column B:D? This is because I want you to increment a value in column A.

[jolivanes]

I don't quite understand your last question.
Attach a small workbook with a before and after.

[asm19]

I don't quite understand your last question.
Attach a small workbook with a before and after.

See Image:

1.png
Basically I want the data:

Column A pass to the column C;
Column B pass to column A;
Column C pass to column B.

Understand?

Another issue, tried to make a loop that increments up 1 unit on No. Order. Amounting in value I get this:

Today Date + 001

It is still not working as I want because it is not to increase 1 unit and is always: 20150825002
File in attach.


Thanks

[jolivanes]

You state:
Column A pass to the column C;
Column B pass to column A;
Column C pass to column B.

In your attachment it is
ID = Column A in Sheet1. In Sheet2 = Column C. OK
No Spools = Column B in Sheet1. In Sheet2 = Column D (Not Column A as you state)
Delivery Length = Column C in Sheet1. In Sheet2 = Column E (Not Column B as you state)

Take your time and let us know what it is.

How do you want to increase the No. Order?
They are not numbers.

[asm19]

The ID is: B0007X0****CU-S
The No Spools is: column B in Sheet1 and column D in Sheet2
Delivery Length is Column C in Sheet1. In Sheet2 = Column E

the code is doing what I want, there is a bad thing.

See the previous screenshot again, and now see this:

2.png

In this screenshot you can see how I want the data because as they were previously, they were in the wrong column, understand?

"How do you want to increase the No. Order?"

Simple, right now I was using today date more "001" in the first line, and the remaining increase always + 1. This why? The order number must always be different from previous ones.
I was previously doing this manually, where the first line would be today date + 001 and incremented in the following.
Example in column A:

Please Login or Register  to view this content.

I can keep doing this manually, but if possible I wanted to leave everything automatic.
You have any different idea?
Thank you

[jolivanes]

Let us know what changes you want made in the attached.

[asm19]

Yes, it works fine and can look like this. I noticed that you use an array to insert, how can I do to enter the remaining data?

Due Date = Today Date
QuantityUnit = M
Type = Order
Remark = Clear
StorageLocation = POGU01
ItemNo = 1

I can not get this to work in array.
Thanks

[jolivanes]

In my last attachment, change the code to this.

Please Login or Register  to view this content.

[asm19]

Yes, that's right. Thank you.
+ Reputation for you

[jolivanes]

Thanks for letting us know and for the rep.
Good luck

[asm19]

Thanks for letting us know and for the rep.
Good luck

I have one more question
With all the data on the sheet 2, I export to xml, but I have a problem. I am creating a table and set the fields for the xml (data_map), but every time you generate new data have to build this table and the "map". There is possibility to block this formatting, not to be changed? Or maybe keep unchanged headers and fill only with data from line 2?
Thanks

[jolivanes]

I'm sorry asm19. I don't know anything at all about that.
Maybe start a new thread so you'll get an answer from someone who knows about this.

Sorry about that.
Good luck

[asm19]

I'm sorry asm19. I don't know anything at all about that.
Maybe start a new thread so you'll get an answer from someone who knows about this.

Sorry about that.
Good luck

Ok, no problem. Thanks for your help.

[jolivanes]

Attached increases the ID numbers also.
I think that's what you wanted isn't it?

[asm19]

Attached increases the ID numbers also.
I think that's what you wanted isn't it?

no jolivanes, version 3 is working as I want. I do not want to increase the ID. I lack only the part of the XML.
Thank you anyway

[asm19]

If you have a new sheet named "XML" that is already formatted with headings and "data_map" XML, if you copy the data sheet "CreateOrders" using a "Copy to Sheet XML" button to XML sheet, from the line 2 down you think you can keep the entire table format? What do you think about this?
Follows the file with what I am talking about.

Livro3_v2.xlsm


EDIT:

I could already do what I wanted, but I have a problem.
The number of orders is not always the same, but can be can be less. When you copy orders to the XML file, if more than the table size, then no problem increases table. The problem is if it is less, when exporting to XML to export the last lines in white, of course I can eliminate these lines manually, but wondered if it is possible to remove blank rows in the table automatically?
File:

Livro 3 - Send.xlsm

EDIT2:

I took care of my problem and is working as I want.
Last question, you know how to lock the position of the buttons so they can not be moved?
Thanks

Livro3 - Send.xlsm

[asm19]

Hello @jolivanes

I want to add one more thing, and would like you to help me up. Basically I want to add a field "Due Date". Before I was putting the "Today Date" in all fields, but now want to put a specific date depending on an ID. See my example:

From:
1.png

To:
2.png


I did manually, put all the dates "by hand", but how can I do this automatically?
Thank you for help.

File
Livro3.xlsm