cut and paste to different worksheet

[Guest]

Hi

Experts
I am working on excel sheet to make a daily production report. In my
worksheet, there are 300 rows selected for a week and column from A to W. Column F is named as "STATUS".

When a particular job finishes, person on the section puts C (C means
Completed) in the column F. What I am after is as soon as column F (Status)
goes C, then information for that job from column A to column W should get
copied automatically to another worksheet 3.

consider in one day 10 jobs gets completed onto a section then 10 jobs
infomation from column A to column W should get copied automatically onto
worksheet 3 as soon as status (column F) gets updated to "C".

Any workable suggestion will be greatly accepted.
Thanking in anticipation!!!

 

[Max]

One play to try ..

Assume source table is in Sheet1, cols A to W, data from row2 down
(Key column is col F, key input in col F is: C)

In an empty col to the right, say col X

Put in X2: =IF(F2="","",IF(F2="C",ROW(),""))

Copy X2 down to say, X100 to cover the max expected data range for the
source table (can copy down ahead of expected data input)

(Leave X1 empty)

In Sheet3
------
Paste the same col headers into A1:W1

Put in A2:

=IF(ISERROR(SMALL(Sheet1!$X:$X,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$X:$X,ROWS($A$1:A1)),Sheet1!$X:$X,0)))

Copy A2 across to W2, fill down to W100
(cover the same range as was done in col X in Sheet1)

Format the cols as per the source cols in Sheet1

Sheet3 will return the desired results, i.e. only the rows from the source
table where the status input in col F in Sheet1 is: C, all bunched neatly at
the top

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi

W. Column F is named as "STATUS".

 

[Guest]

hi
Max
Thnx for your suggestion. I tried your solution. I think you gave me correct
solution but I didn't tell you that in my worksheet 1, I have VB script and
conditional formatting which evaluates if I put some variables in cells.

First of all, information in columns from A to E gets filled automatically,
If I run query developed in MS ACCESS. I have recorded macro for that and
allocated a button to it.
Say I have data from A4 to E4
A104 to E104 (filled in automatically after
running macro)

In column B heading is "Customer Name" and (B4 to B104) change colour
depending on the customer name.
In column D heading is "Due Date" which has 3 condition in it based on "if
function" and changes colour if evaluates true.
column F is what I am trying to utilize as Key column for my trick.
column G has VB script and cnditional formatting related to it. Column H
gets filled up automatically depending on the result of the column G.
column I and J have again VB script and conditional formatting related to
them. column K gets filled automatically depending on result of column I &
J.
column L & M have VB script and conditional formatting related to them.
column N gets filled automatically depending on result of column L & M.
column Q conditons is based on the result of column G & column(I or J) &
column (L OR M).
So your solution is not working as it should have. What I was thinking to
write VB script something like that

If( Target . Column = 6) then (here A=1, B=2, C=3, ...., F=6, ....., Z=26)
if Target. Value = "C" then
Select entire Row( I don't know how to select that entire row using VB
commands)
select cut ( I think like Selection.Cut)
make sheet 3 active ( don't know)
select next available blank row(don't know)
select paste (selection.paste)
save sheet3 (don't Know)
make sheet 1 active ( don't know)
save sheet 1 as well (don't know)

If you can help me I will be very thankful.
Thanking you in anticipation!!!

 

[Max]

You're welcome. But I'm not able to help you with the vba Qs. Do hang around
awhile for possible insights from others to these. Or, consider putting in a
fresh post in .programming.

 

[Guest]

thnx max for your efforts
cheers

Hi
When a particular job finishes, person on the section puts C (C means
Completed) in the column F. What I am after is as soon as column F (Status)
goes C, then information for that job from column A to column W should get
copied automatically to another worksheet 3.

consider in one day 10 jobs gets completed onto a section then 10 jobs
infomation from column A to column W should get copied automatically onto
worksheet 3 as soon as status (column F) gets updated to "C".

Any workable suggestion will be greatly accepted.
Thanking in anticipation!!!

 

[Max]

You're welcome !