cut and paste to different worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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".
Click to expand...
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!!!
 
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
----
MINAL ZUNKE said:
Hi
Click to expand...
W. Column F is named as "STATUS".
 
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!!!
 
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.
 
thnx max for your efforts
cheers

MINAL ZUNKE said:
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!!!
Click to expand...
 
Back
Top