Solved: Column search returning wrong results

Steve,
You were collecting a few extra columns here
[VBA]For Each CLL In MasterWS.Range("G3", MasterWS.Cells(MasterWS.Rows.Count, 1).End(xlUp))[/VBA]

Malcolm, do you have any idea why the number 10 is not working in the code I tried.....just a mystery to me.......it is in the first module of the file posted at 13....

You're collecting 10s from column B, See my last post.

dang......shame on me
[VBA]Each CLL In MasterWS.Range("G3", MasterWS.Cells(MasterWS.Rows.Count, 7).End(xlUp))[/VBA]

thanks malcolm

For the original poster.....Malcolm's code is much cleaner....I like mine because I use it often and am (theoretically)more familiar with it.

No problems with your version Steve, but loops can be slow with many rows. eg checking 20,000 rows 12 times for each value, so I though I'd toss in a filter solution.
For another variation with sorted data, you could find the first and last positions of each value and use that to define the rows to be copied.

Lucus, the script is pulling all those random lines in M10 because in the operation column, there is a 10, and one was there because the quantity column was 10, somehow its pulling from all cells.

Adonaioc, I'm sorry I doubted you.....I missed that and you were right.

Guess if you have any more questions you will ask......Malcolm has explained the advantage of his code.....

Here it is fixed....

Malcolm, i tried to use your script but i got an error and it did something to the couple hundred rows, they are now missing, and I cannot unhide them.

runtime error 1004 no cells found

when i hit debug it highlights

.Range("A3:K" & LRw).SpecialCells(xlCellTypeVisible).Copy


i dont know how to get the cells back either

If rows are hidden, remove the filter.
Can you repost your workbook so I can test the code later?

If there is no data for a filter value, then you would get that error when you try and grab the visible cells.

Attachment 8368

Here it is I cleared out my old macros, the one Malcolm posted is copytoneworksheets2

Check for data before running the filter
[VBA]
'Copy selected data
For i = 1 To 12
If Application.CountIf(Rng, i) > 0 Then
Rng.AutoFilter Field:=1, Criteria1:=CStr(i)
.Range("A3:K" & LRw).SpecialCells(xlCellTypeVisible).Copy
Sheets("M" & i).Range("A3").PasteSpecial xlAll
End If
Next

[/VBA]

That works great now, one more question, I have it set to copy the first 30 rows from each M* sheet to the daily sheet under the appropriate machine, but I want a way to limit how many rows it pulls. for instance, column J totals the run time per machine, say we are running a 8 hour day how can i tell it to pull only until the total for that machine in column J jumps above 8 hours then stop. say M5 looks like this

job1 2 hours
job2 4 hours
Job3 1.5 hours
job4 3 hours pull this job but no more
job5 13 hours
job6 4 hours

totalling 10.5 hours but do not stop pulling to the daily sched maching 5 list until the total in column j is greater than 8

but I still need it to pull in larger jobs like the 13 hours one even though it alone is greater than 8 hours, when it is the first one on the list.

I hope that makes sense.

Attachment 8372

Your post 34 is a bit of a logic puzzle. Please take the time to make things simple for me to follow. Add comments/highlight your sample showing what should be copied and to where.