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 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 26, 2014 at 03:30 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 26, 2014 at 03:30 AM
Related:
- Automatic copy on excel
- Nvidia automatic driver detection tool - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 12, 2014 at 05:44 AM
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 ?????????
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 ?????????
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 15, 2014 at 02:14 AM
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
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
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 26, 2014 at 03:30 AM
Jul 26, 2014 at 03:30 AM
whatever is filtered is copied by the macro. change the filter field and criteria to suit you
Jul 14, 2014 at 09:35 PM
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