Track movement stock by copy amounts from previous row to next row.

Hi experts
the result should be in FINAL sheet from row7 based on cell A2 should match with column B for each sheet then should brings data:
1-in column A will show sheet name and brings data for old date .
2- in column B will brings INVOICE.N from each sheet is existed in column C based on adjacant cell contains sheet name in column A
3- in column C will brings DATE from each sheet is existed in column A based on adjacant cell contains sheet name in column A for FINAL sheet.
2- in column D will brings CUSTOMER from each sheet is existed in column D based on adjacant cell contains sheet name in column A .
4- in column E in row8 will brings QTY from STOCK sheet in Column (C) .
5-in column F in row2 will brings UNIT PRICE from STOCK sheet in Column (D) .
6- in column(G) will brings QTY from each sheet in column(E) based on adjacant cell contains sheet name in column A for FINAL sheet .
7- in column(H) will brings UNIT PRICE from each sheet in column(F) based on adjacant cell contains sheet name in column A for FINAL sheet .
8- in column(I) will calculation QTY based on sheet name , if sheet name in column A for FINAL sheet is
PURCHASES then the I=E +G
if sheet name in column A for FINAL sheet is
SALS then the I=E - G, if sheet name in column A for FINAL sheet is PURCHASES RETURNS then the I=E - G , if sheet name in column A for FINAL sheet is
SALES RETURNS then the I=E +G
9-column(J) should be the same column(H)
next row(8) depends on row7 then will move numric valus from row7 to row 8
in column( I ) in row7 will copy to column( E )for row8,
in column( H ) in row7 will copy to column( F )for row8,
in columns( G,H) will brings QTY,PRICE from sheet name is mathed with column A for row8,
in column( I ) will calculation QTY for row8(CHECK POINT 8) .
in column( J ) for row8 (check point9)

There are inconsistencies betweeh dates in the individual sheets and those in FINAL: DD/MM/YYYY are changed to mm/dd/yyyy

Please Login or Register  to view this content.

This uses a (hidden) sheet "TEMP": do NOT delete

Hi John again ,

There are inconsistencies betweeh dates in the individual sheets and those in FINAL: DD/MM/YYYY are changed to mm/dd/yyyy

sorry about it.

where did you get value 20 in PREVIOUS QTY column from ?!
also value 100 in QTY column as in picture 1?!
it should be 200 as in picture 2 in PREVIOUS QTY when brings from STOCK sheet ,500 as I in picture 3 when brings from PURCHASES sheet .
did you calculate across sheets for the same date or..?
if it's so it doesn't what I want .
every date for each sheet alone should show as I did it in OP .

Look CAREFULLY at the brand

BS 1200R24 G580 JAP not BS 1200R20 G580 JAP

OMG !
works perfectly , but I have question about Temp sheet, what does do this sheet?
is it necessary ?
and can avoid error when A2 is empty or error brand? .should clear data from row 7 if A2 is empty or error brand.

The macro uses Advanced Filter (very good for performance) to extract data from each sheet and puts the extracted data into TEMP. You need the data collected too ensure it is in data order

So TEMp is need (and can be hidden): if you do not like the solution, then hope that someone else provides one ( Scripting Dictionary? )as I am not going to spend any more time on this.

And I can put an "error trap" when A2 is blank but why would you run the macro if it is blank?

Please Login or Register  to view this content.

See attached with updated code.

Please Login or Register  to view this content.

Please try the attached which removes need for "Temp" sheet

It currently sorts oldest to latest date

A sample (I manipulated the dates purely to show an example): is this what is required?

If so, post a workbook with a real example, so I can test it with actual data.

Try with attached:

See attached: