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

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

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

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
---

Copy Rows with a cell value 0 from several worksheets to a ne
 

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" wrote:

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
---

Copy Rows with a cell value 0 from several worksheets to a ne
 

.. 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
---

Copy Rows with a cell value 0 from several worksheets to a ne
 

Max,

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

"Max" wrote:

.. 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
---