Copy Rows with a cell value >0 from several worksheets to a new sh

[Skeletor]

Hi, (Again).
Thanks for your help and patience by the way!

I copied it in exactly as described and it came up with a Compile error:
Block If without End If.

I added another End If at the end, closed the editor and saved the workbook.

When I typed in "1" in the quantity (F) column, nothing happened. However,in
the first row in Column A on the Job List worksheet this appeared;
"If Target.Cells.Count > 1 Or IsEmpty(Target) Or ActiveSheet.Name =
(MySheet)"
"Then Exit Sub" appeared in the second row on the same page.

What am I doing wrong?
Best Wishes
Mike

 

[Max]

Posted this response in your original thread:
------------------------------------------------------
I was able to get Mike H's sub up and working nicely,
thought I'd lend a helping hand here ..

Here's the working sample, with the sub implemented:
http://freefilehosting.net/download/40ag7
Sub to copy qty more than zero.xls

Try this play to set it up:
First, insert a new sheet in your book, name it as: Summary
This is the destination sheet where all of the lines with Qty > 0 (Qty is
assumed in col F) from all other sheets will be auto-copied to, once the qty
is input.

Then install Mike H's sub below ..
Right-click on the Excel icon (just to the left of File on the main menu) >
View Code
This brings you directly to the ThisWorkbook module
Copy n paste the code below into the whitespace on the right
Then press Alt+Q to get back to Excel
Test it out, input values > 0 in Qty (col F) in the input sheets

'-------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Mike H
MySheet = "Summary"
MyColumn = "F:F"

If Target.Cells.Count > 1 Or IsEmpty(Target) _
Or ActiveSheet.Name = (MySheet) _
Then Exit Sub

If Not Intersect(Target, Range(MyColumn)) Is Nothing Then
If IsNumeric(Target) And Target.Value > 0 Then
Application.EnableEvents = False
Target.EntireRow.Copy
lastrow = Sheets(MySheet).Cells(Rows.Count, "A").End(xlUp).Row
Sheets(MySheet).Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End If
End Sub
'------

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik
---

 

[Skeletor]

Max and Mike

Thankyou for your help. But for some stupid reason, it doesn't work on my
computer at home, (where I am now), or the computer at work (which doesn't
have internet access by the way).

Is there something I need to do somewhere that will enable this code to
work? What am I doing wrong?. Even the sample workbook you created for me
isn't working and I know its not your fault. Its something I have not done.

Please help again. Thankyou
Mike W

 

[Max]

.. Its something I have not done ..

Could it be your Excel setting that files are opened with macros disabled?

Click Tools > Options > Security tab > Macro Security
Set it to "Medium" > OK

Then try opening the file,
you should see a Security Warning dialog
Click on "Enable Macros", and test it out ..
It should work fine for you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik
---

 

[Skeletor]

Max,

Thankyou again. It works perfectly on the home computer. I will try it out
at work tomorrow.
Thankyou,
Mike W