Dear all and JBM

I posted my problem two days ago, but I didn't have chance to check the
answer until now. In the mean while the topic is already on the 3-4 page.
Therefore I copied it once again at the end of this post. JBM answered me,
but this is not exactly what I need. First of all, I would need to copy data
in order to put it in the new sheet and I would like to have a €œreal time€�
update over the situation from the two processes; in case that an order is
delayed or the sequence is altered.

Secondly, I would like to combine it with the data from second sheet
(€œProcess 2€�, please see below) into a list.

I'm not very experienced Excel user, so I have no idea what this would
require. Please ask more question if my explanation below is not clear and I
will try to be more often online.

Cheers
Vedad


"JMB" wrote:
One way is to use Excel's Autofilter (Data/Filter/Autofilter). Filter the
Finish Good column for "YES". Then copy the filtered list to a new sheet.


"Vedad" wrote:

Hello everyone

I have a following problem:

- Two processes, which are recorded on two different sheets
- Some of the designs need to be worked on once, twice or several times
before they are finished goods.

SHEET 1 (Proces1)
Design nr. Name Finish good Time finish

1 Name1 07:00
2 Name2 08:00
3 Name3 09:00
1 Name1 YES 10:00
4 Name4 11:00
2 Name2 YES 12:00
4 Name4 13:00
3 Name3 14:00
3 Name3 YES 15:00
4 Name4 YES 16:00


SHEET 2 (Proces2)
Design nr. Name Finish good Time finish

5 Name5 08:00
6 Name6 09:00
7 Name7 YES 10:00
6 Name6 11:00
8 Name8 12:00
6 Name6 YES 13:00
5 Name5 YES 14:00
8 Name8 15:00
8 Name8 YES 16:00


What I would like to construct is third sheet which is updated automatically
with the overview of times of finished goods. I made a very simple example
above, where in reality the sheets contain many more designs and information
which I would like to eliminate by making a new and simple list. Something
that looks like this:

1 Name1 YES 10:00
7 Name7 YES 10:00
2 Name2 YES 12:00
6 Name6 YES 13:00
5 Name5 YES 14:00
3 Name3 YES 15:00
4 Name4 YES 16:00
8 Name8 YES 16:00

The sequence of finished goods coming at the same time has no importance.
The finish times are important in order to plan the processes to follow.

If the function of combining data from two sheets into one list is two
difficult to do, I could live with two separate lists for Proces1 and
Proces2.

The way I see it is to €œsearch€� for YES and copy the line or specific cells
into a new arc, but I dont have an idea for how to do it.

Any ideas are highly appreciated.

Regards
Vedad

try:

Sub tst()

Dim t, r, nbr, mySets(100, 100) As Variant

nbr = 0
Sheets("Sheet1").Select
r = Range("A65500").End(xlUp).Row
For t = 1 To r
If Cells(t, 3) = "YES" Then
nbr = nbr + 1
mySets(nbr, 1) = Cells(t, 1)
mySets(nbr, 2) = Cells(t, 2)
mySets(nbr, 3) = Cells(t, 3)
mySets(nbr, 4) = Cells(t, 4)
End If
Next

Sheets("Sheet2").Select
r = Range("A65500").End(xlUp).Row
For t = 1 To r
If Cells(t, 3) = "YES" Then
nbr = nbr + 1
mySets(nbr, 1) = Cells(t, 1)
mySets(nbr, 2) = Cells(t, 2)
mySets(nbr, 3) = Cells(t, 3)
mySets(nbr, 4) = Cells(t, 4)
End If
Next

Sheets("Sheet3").Select
For t = 1 To nbr
Cells(t, 1) = mySets(t, 1)
Cells(t, 2) = mySets(t, 2)
Cells(t, 3) = mySets(t, 3)
Cells(t, 4) = mySets(t, 4)
Next

r = Range("A65500").End(xlUp).Row
Range("A1:D" & r).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select

End Sub

Thank you for quick answer. Looks like something that might work. The only
problem is that I'm not very familiar with programming in VB.

Could you give me some tips what I need to read about in order to solve this
problem; and what are parameters I need to change in your proposal?

Thanks once again.
--
Vedad



"excelent" skrev:

try:

Sub tst()

Dim t, r, nbr, mySets(100, 100) As Variant

nbr = 0
Sheets("Sheet1").Select
r = Range("A65500").End(xlUp).Row
For t = 1 To r
If Cells(t, 3) = "YES" Then
nbr = nbr + 1
mySets(nbr, 1) = Cells(t, 1)
mySets(nbr, 2) = Cells(t, 2)
mySets(nbr, 3) = Cells(t, 3)
mySets(nbr, 4) = Cells(t, 4)
End If
Next

Sheets("Sheet2").Select
r = Range("A65500").End(xlUp).Row
For t = 1 To r
If Cells(t, 3) = "YES" Then
nbr = nbr + 1
mySets(nbr, 1) = Cells(t, 1)
mySets(nbr, 2) = Cells(t, 2)
mySets(nbr, 3) = Cells(t, 3)
mySets(nbr, 4) = Cells(t, 4)
End If
Next

Sheets("Sheet3").Select
For t = 1 To nbr
Cells(t, 1) = mySets(t, 1)
Cells(t, 2) = mySets(t, 2)
Cells(t, 3) = mySets(t, 3)
Cells(t, 4) = mySets(t, 4)
Next

r = Range("A65500").End(xlUp).Row
Range("A1:D" & r).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select

End Sub

Anybody?

A tip what and where I should start looking at would be appriciated.

Thanx
Vedad

hi vedad
ok back from work :-)

do u mean help to put kode in a module and use or...?

Hi excelent

I'm a new bee (total idiot) in VBA programming. Until now I just used some
easy formulas in Excel sheets, but I have an ambition about learning more
about VBA and more advanced functions in Excel.

Since I'm so inexperienced, I was not sure where to start (and I was hoping
that it can be solved easily with some embedded Excel functions) Therefore, I
presented the whole problem hoping that somebody will help me at least in
which direction to go.

You gave me the whole code, and it might be just what I need, but I need
just a little bit more information what this code does and what I need to
learn about. I already got some books on Excel and will throw myself into
reading as soon I figure out what I need to know :-)

--
Vedad



"excelent" skrev:

hi vedad
ok back from work :-)

do u mean help to put kode in a module and use or...?

First select kode and copy
in excel press ALT+ F11
paste kode in window on right side
back to excel
press ALT+F8
select macro (tst)
and press run

the macro copy ur values in sheet1 and sheet2
where column C is = YES
and put it on sheet3 and make a sort of column D then B

bout learning vba
try look in here, there is a lot to learn just reding
the questions and answer.

sry my french - from Denmark :-)

Hej excelent

mange tak din hjælp. Jeg vil lige sætte mig lidt mere i VBA og afprøve din
kode. Det kan godt være at virker.

Hilsen
--
Vedad


"excelent" skrev:

First select kode and copy
in excel press ALT+ F11
paste kode in window on right side
back to excel
press ALT+F8
select macro (tst)
and press run

the macro copy ur values in sheet1 and sheet2
where column C is = YES
and put it on sheet3 and make a sort of column D then B

bout learning vba
try look in here, there is a lot to learn just reding
the questions and answer.

sry my french - from Denmark :-)