Automatic copy on excel

Closed
belonio Posts 1 Registration date Friday July 11, 2014 Status Member Last seen July 11, 2014 - Jul 11, 2014 at 09:12 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 26, 2014 at 03:30 AM
hi sir/mam,

i want to automatic copy a data from sheet to another. all those qty on out column in big warehouse sheet must be copy to small warehouse as IN.
example.

sheet1 named big warehouse
date item code in out
june 1 4325 100 50
june 3 1234 500
june 4 785 36 5
june 12 963 56
june 16 50 123

sheet 2 named small warehouse
date item code in
june 1 4325 50
june 4 785 5


thank you

4 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 12, 2014 at 05:44 AM
what you want is not clear particularly the term "automatic"

1. in second sheet you have written "in" is it in or out

2.in E2 of second sheet (to check values in column D) type this formula

=INDEX('big warehouse'!$E$2:$E$6,MATCH(1,('big warehouse'!$A$2:$A$6=A2)*('big warehouse'!$B$2:$B$6=B2)*('big warehouse'!$C$2:$C$6=C2),0))

INVOKE THIS FORMULA BY CONTROL SHIFT ENTER

copy E2 in second sheet FROM e3 DOWN

is this what you want ?????????
0
Hi Sir/Ma'am,

For clearer question.

Sheet1 named Big Warehouse(please see below)

A B C D
date item code IN OUT
June 1, 2014 A4325 100 50
June 3, 2014 B1234 500 0
June 4, 2014 E785 36 5
June 12, 2014 H963 56 0
June 16, 2014 K50 123 0


I want to automatic copy all rows of sheet1 (big warehouse) that have the column D(out) = >0 to worksheet sheet2 (small warehouse) as IN on column C. And each time I insert new row of sheet1 with column D(out) = >0 it will automatic update sheet2.

result must be like this.
Sheet2 named Small Warehouse.

A B C
date item code IN
June 1, 2014 A4325 50
June 4, 2014 E785 5



Thank you
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 15, 2014 at 02:14 AM
still you have not cleared what is meant by automatic

however if you run this macro automatically you will get what you want. check

Sub test()
Dim r As Range
Worksheets("small warehouse").Cells.Clear
With Worksheets("big warehouse")
Set r = .Range("A1").CurrentRegion
r.AutoFilter field:=4, Criteria1:="<>" & 0
r.SpecialCells(xlCellTypeVisible).Copy
With Worksheets("small warehouse")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
0
hi,

I have tried the macro above. yes it copied the rows to small warehouse which had a out qty from big warehouse . correct copy only those rows which has a qty OUT on big warehouse but as qty IN on small warehouse no more OUT column.
only those rows which has a qty on OUT column in big warehouse will be copied to small warehouse as qty on IN column
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 26, 2014 at 03:30 AM
whatever is filtered is copied by the macro. change the filter field and criteria to suit you
0