copy data from multiple sheets to one and update inventory

**MKLAQ** · 03-08-2021, 04:20 AM

hello
i have many sheets " purchase , sales, returns sales ,returns purchase " every in voice in theses sheets should copy to sheet data i put the result in sheet data and at the same time updating the inventory in sheet stock it should subtracting the quantity from "sheets sales " &" returns purchase " and add to the quantity from sheets " returns sales " and " purchase "
if my post is not clear please tell me
thanks in advance

**MKLAQ** · 03-09-2021, 04:07 AM

any helps experts?

**MKLAQ** · 03-13-2021, 05:51 AM

any helps guys?

**sintek** · 03-13-2021, 07:11 AM

Small datasets so this easy enough to understand...No need for array...
Sheets Data & Returns Sales has trailing space...

Please Login or Register  to view this content.

**MKLAQ** · 03-13-2021, 08:16 AM

wow ! it's great about sheet data but I need update in sheet inventory when fill invoices from four sheets (purchase , sales , returns purchase,returns sales) should summing and subtracting I put some comments inside in the file i no Know if the explanation is not clear please tell me if it's not clear

**sintek** · 03-13-2021, 08:26 AM

Update stock sheet with expected result...And is this overwritten all the time?

**MKLAQ** · 03-13-2021, 08:33 AM

yes it should do that automatically when change in four sheets

**sintek** · 03-13-2021, 08:34 AM

So...Provide sample with expected result in Stock sheet so I can see...

**MKLAQ** · 03-13-2021, 08:58 AM

please check my file and some explanation

**sintek** · 03-13-2021, 09:16 AM

Is data sheet a history of all individual transactions of the 4 sheets...

**MKLAQ** · 03-13-2021, 09:25 AM

you mean sheet "stock" ?

**sintek** · 03-13-2021, 09:28 AM

No....Data....
Does data carry on growing all the time ...

**MKLAQ** · 03-13-2021, 09:33 AM

yes of course it will increase the data my uploaded file is a simple to understand what I want

**sintek** · 03-13-2021, 09:48 AM

Please Login or Register  to view this content.

**MKLAQ** · 03-13-2021, 09:55 AM

thanks I will test your code and I'll inform you what happens it takes some time

**MKLAQ** · 03-13-2021, 10:10 AM

it's perfect , but the code doesn't deal if I have no data in any invoice for instance If I fill data today sales but there is no invoices purchase or returns then the code will shows error in this line Application-defined or Object-defined error

HTML Code:

and crashed sometimes I have sales and purchase and often have returns and purchase without sale so may you fix it please ?

**sintek** · 03-13-2021, 10:18 AM

Oops forgot the red part...see amended code above...

**MKLAQ** · 03-13-2021, 10:34 AM

I note it gives the wrong values in sheet stock I put the right value for one brand in column n just I would for you click the macro and compare with column n to see where is the problem

**sintek** · 03-13-2021, 10:42 AM

Change this snippet to suite...

Please Login or Register  to view this content.

If Transaction must - then add * -1...if not remove it...

No time now...can see Monday

**MKLAQ** · 03-13-2021, 10:58 AM

thanks for your time I will test it and inform you

**MKLAQ** · 03-13-2021, 11:05 AM

unfortunately it doesn't work

**sintek** · 03-13-2021, 11:37 AM

What is calculation

If PUR - add to stock
If SALE - subtract from stock
If RPUR - add to stock
If RSALE - subtract from stock

or...

This gives correct answer

Please Login or Register  to view this content.

**MKLAQ** · 03-13-2021, 11:44 AM

calculation should be

If PUR - add to stock
If SALE - subtract from stock
If RPUR - subtract to stock
If RSALE - add from stock

**sintek** · 03-13-2021, 11:47 AM

Originally Posted by MKLAQ

calculation should be

If PUR - add to stock
If SALE - subtract from stock
If RPUR - subtract to stock
If RSALE - add from stock

K post 22 snippet change solves then...

**MKLAQ** · 03-13-2021, 12:07 PM

million thanks for your solution you give me a big favor your legend

just I'm asking for clear the data after run macro should clear this range from a20: g for these sheets after copy ("Purchase", "Sales", "Returns Purchase", "Returns Sales ")
i try add this line

HTML Code:

to become like this

HTML Code:

but it clears also the headers and sometimes gives me error , the aim that maybe I repeat running macro Unintentionally then it summing and subtracting repeatedly and gives me wrong values in stock
thanks again

**sintek** · 03-13-2021, 01:35 PM

Close...

Please Login or Register  to view this content.

**MKLAQ** · 03-13-2021, 03:54 PM

@sintek actually I was wrong about clear this range a20:g it supposes a20:e because F,G contains formulas
also when I clear the range it also delete formatting data and delete dropdwn in col B,C,D after many tries I've found this properties

HTML Code:

it just clear the contents , and it successes also if I run the macro without there are data in invoices then it shows error in this line

HTML Code:

I added this line before it

HTML Code:

also it successes I try learning simple things now every thing works excellently that's because of you and I attach final file to others members take advantage from this project
thank you so much for support me

**sintek** · 03-14-2021, 07:38 AM

Glad you got it sorted...Tx for rep +

copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Re: copy data from multiple sheets to one and update inventory

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] merge duplicated data across multiple sheets and sum & subtracting (update inventory)

[SOLVED] Get data from multiple sheets and update in summary sheet

[SOLVED] Multiple sheets data update in result sheet

[SOLVED] how to automatically update data in multiple sheets

Bookmarks

Bookmarks

Posting Permissions