Split invoice file in max 1000 line items

[Crispy85]

Hi,

I want to ask the guru's for help since i am inexperienced enough to find this out by myself

I have a file with multiple invoices (Invoiceno is in column E).
For upload reasons in SAP, there can only be max 999 items in one sheet (to simply copy and paste from excel in SAP).

So, this list has to be split in different sheets when the number of rows is >999.

For example: if invoice 1 has 700 items and invoice 2 has 500 items, combined the total is 1200. But it should not take 299 from invoice 2 and paste the rest in a new sheet. In this case, it should copy the whole invoice 2 in a new sheet. It is allowed to have multiple invoices in 1 sheet, if 1) the invoices are complete and 2) the amount of rows is <999.

I hope i am clear.
I also added a sample file, how the layout is and with 2304 rows.

Thanks for all your help!!!

[Crispy85]

I came up myself with this solution, but it just endlessly keeps cutting and pasting in to new sheets into oblivion.

Please Login or Register  to view this content.

What do you guys think what's wrong with this code?

[Crispy85]

Anybody?

[event21]

Hi -

Please try this one and let me know.
Data in sheets(1)
Note: Data will be remove so please run in the test file first.

Please Login or Register  to view this content.

event

[Crispy85]

Hi Event,

Thanks!
It works!
The only problem that i'm having with my testfile, is that it runs pretty slow.
It took about 2 minutes for it to complete. Do you know what could be the reason?

[event21]

Hi -

The deletion of entire row takes time.
try this one

Please Login or Register  to view this content.

to delete just the affected ranges


event

[Crispy85]

Ok. I will try your last one too.

Could you tell me why mine isn't working? It seems simple but it gives me the Compile error: else without if.
Even if it's fixed, i don't know if it would work.

Please Login or Register  to view this content.

[event21]

Hi -

This one is wrong, there is no more IF in the succeeding elseif

Please Login or Register  to view this content.

event

[Crispy85]

Thanks! I will check it out.
Your code works smooth now! Thanks!!

[Crispy85]

Hi Event,

I tried your code with a real invoice document, which ofcourse i can't include, and the first part is 998 rows as it should be, but AP002 has only 443 rows, while AP003 has 70 rows. So it shouldn't have made the split and only keep AP002 with the total of 513 rows.
Do you know what causes this?

[event21]

Hi -

I am not sure about it, what you can do is to upload the file (not the real data). All you need to do is to change the data (keep the formatting).

event

[Crispy85]

Ok, i will Event. Thanks.
First enjoy my weekend

[Crispy85]

Goodmorning Event,

Please find the invoice attached. I replaced all the information with "test". For the invoiceno i used 1, 2, 3 etc.

Thanks for all your help!!

[event21]

Hi -

Try

Please Login or Register  to view this content.

event

[Crispy85]

Hi Event,

Looks pretty awesome to me! Thank you so much, i wll try this with multiple files to see if everything is going alright.
Many thanks again.

Regards,
Crispy85

[Crispy85]

Hi Event,

I don't know whats wrong, but i tested it with another invoice file, that has 2377 line items, and it doesn't make the split...
Would you please be so kind to check?

Thank you

[event21]

Hi -

Opppsss, try this one.

Please Login or Register  to view this content.

event

[jolivanes]

Re: First enjoy my weekend

Where did you go? Havermarkt?

[Crispy85]

Where did you go? Havermarkt?

Haha, so you are familiar :D
I'm at the Havermarkt pretty often indeed.

[Crispy85]

Hi Event,

The script is looking good! The other invoice now also comes out properly.
Thank you!!