Skeletor" wrote:

I'm sorry to say, this didn't work. But I believe it is due to my lack of
understanding, so i ahve a couple of questions;
1)When I change the name of "MySheet", do I call it "Sheet10", as it is
listed, or do I call it "Job List", as I have renamed the sheet?
2) What name do I give "MyColumn". The first row on each worksheet contains
the column headings. Since the "Quantity" column is Column F on each of the
10 worksheets, what reference do I actually use?
3) In the statement; MyColumn = "D:D", what does the "D:D" stand for?
4)MySheet="Sheet2". Do I change this to; Job List="Sheet10"?

Any help will be greatly appreciated as the Boss is getting impatient.
Thankyou
Mike


"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Double Click 'This Workbook' and paste this in
on the right.

Change MySheet to the name of the sheet you want to copy to data to
Change MyColumn to the column where you will enter the quantity. Every time
you enter a greater than Zero entry in MyColumn it will copy the entire row
the MySheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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


Mike


"Skeletor" wrote:

I have a series of worksheets that contain different product lines. When I
type in an amount in the "Quantity" column in each worksheet, I would like
that row to be inserted into a new worksheet automatically.

The problem is; I would like the new worksheet to contain only the rows from
each worksheet that have a "Quantity" value 0. The worksheet must fill from
the top row down and each new entry must be inserted in the next blank row
down the page. That way, I am printing a sheet that only contains the
selected items.

Any help will be greatly appreciated. Thankyou

Hi (again),

Follow these installation instructions precisely.

1. Alt + Fll to open VB editor
2. Double click 'This workbook' and paste the code in on the right hand side
3. Change this line MySheet = "Sheet2" from "Sheet2" to the name of the
sheet data is to be copied to. For example - MySheet = "SheetXYZ" You don't
alter the word MySheet just the bit between the quotes.
4. Change this line MyColumn = "D:D" to the column on you worksheets where
you enter the total. for example for Column F - MyColumn = "F:F" You don't
alter the word MyColumn just the bit between the quotes.
5. Don't change anything else
6. Close VB editor and save the workbook

If you now enter data into you worksheets as soon as you enter a value 0
into the quantity column you data will be copied to the sheet you selected in
Step 3 above

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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

Mike


"Skeletor" wrote:

Skeletor" wrote:

I'm sorry to say, this didn't work. But I believe it is due to my lack of
understanding, so i ahve a couple of questions;
1)When I change the name of "MySheet", do I call it "Sheet10", as it is
listed, or do I call it "Job List", as I have renamed the sheet?
2) What name do I give "MyColumn". The first row on each worksheet contains
the column headings. Since the "Quantity" column is Column F on each of the
10 worksheets, what reference do I actually use?
3) In the statement; MyColumn = "D:D", what does the "D:D" stand for?
4)MySheet="Sheet2". Do I change this to; Job List="Sheet10"?

Any help will be greatly appreciated as the Boss is getting impatient.
Thankyou
Mike


"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Double Click 'This Workbook' and paste this in
on the right.

Change MySheet to the name of the sheet you want to copy to data to
Change MyColumn to the column where you will enter the quantity. Every time
you enter a greater than Zero entry in MyColumn it will copy the entire row
the MySheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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


Mike


"Skeletor" wrote:

I have a series of worksheets that contain different product lines. When I
type in an amount in the "Quantity" column in each worksheet, I would like
that row to be inserted into a new worksheet automatically.

The problem is; I would like the new worksheet to contain only the rows from
each worksheet that have a "Quantity" value 0. The worksheet must fill from
the top row down and each new entry must be inserted in the next blank row
down the page. That way, I am printing a sheet that only contains the
selected items.

Any help will be greatly appreciated. Thankyou

Thanks again Mike.
I'll give it another try.

"Mike H" wrote:

Hi (again),

Follow these installation instructions precisely.

1. Alt + Fll to open VB editor
2. Double click 'This workbook' and paste the code in on the right hand side
3. Change this line MySheet = "Sheet2" from "Sheet2" to the name of the
sheet data is to be copied to. For example - MySheet = "SheetXYZ" You don't
alter the word MySheet just the bit between the quotes.
4. Change this line MyColumn = "D:D" to the column on you worksheets where
you enter the total. for example for Column F - MyColumn = "F:F" You don't
alter the word MyColumn just the bit between the quotes.
5. Don't change anything else
6. Close VB editor and save the workbook

If you now enter data into you worksheets as soon as you enter a value 0
into the quantity column you data will be copied to the sheet you selected in
Step 3 above

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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

Mike


"Skeletor" wrote:

Skeletor" wrote:

I'm sorry to say, this didn't work. But I believe it is due to my lack of
understanding, so i ahve a couple of questions;
1)When I change the name of "MySheet", do I call it "Sheet10", as it is
listed, or do I call it "Job List", as I have renamed the sheet?
2) What name do I give "MyColumn". The first row on each worksheet contains
the column headings. Since the "Quantity" column is Column F on each of the
10 worksheets, what reference do I actually use?
3) In the statement; MyColumn = "D:D", what does the "D:D" stand for?
4)MySheet="Sheet2". Do I change this to; Job List="Sheet10"?

Any help will be greatly appreciated as the Boss is getting impatient.
Thankyou
Mike


"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Double Click 'This Workbook' and paste this in
on the right.

Change MySheet to the name of the sheet you want to copy to data to
Change MyColumn to the column where you will enter the quantity. Every time
you enter a greater than Zero entry in MyColumn it will copy the entire row
the MySheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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


Mike


"Skeletor" wrote:

I have a series of worksheets that contain different product lines. When I
type in an amount in the "Quantity" column in each worksheet, I would like
that row to be inserted into a new worksheet automatically.

The problem is; I would like the new worksheet to contain only the rows from
each worksheet that have a "Quantity" value 0. The worksheet must fill from
the top row down and each new entry must be inserted in the next blank row
down the page. That way, I am printing a sheet that only contains the
selected items.

Any help will be greatly appreciated. Thankyou

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. What am I
doing wrong?

Regards
Mike W

"Mike H" wrote:

Hi (again),

Follow these installation instructions precisely.

1. Alt + Fll to open VB editor
2. Double click 'This workbook' and paste the code in on the right hand side
3. Change this line MySheet = "Sheet2" from "Sheet2" to the name of the
sheet data is to be copied to. For example - MySheet = "SheetXYZ" You don't
alter the word MySheet just the bit between the quotes.
4. Change this line MyColumn = "D:D" to the column on you worksheets where
you enter the total. for example for Column F - MyColumn = "F:F" You don't
alter the word MyColumn just the bit between the quotes.
5. Don't change anything else
6. Close VB editor and save the workbook

If you now enter data into you worksheets as soon as you enter a value 0
into the quantity column you data will be copied to the sheet you selected in
Step 3 above

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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

Mike


"Skeletor" wrote:

Skeletor" wrote:

I'm sorry to say, this didn't work. But I believe it is due to my lack of
understanding, so i ahve a couple of questions;
1)When I change the name of "MySheet", do I call it "Sheet10", as it is
listed, or do I call it "Job List", as I have renamed the sheet?
2) What name do I give "MyColumn". The first row on each worksheet contains
the column headings. Since the "Quantity" column is Column F on each of the
10 worksheets, what reference do I actually use?
3) In the statement; MyColumn = "D:D", what does the "D:D" stand for?
4)MySheet="Sheet2". Do I change this to; Job List="Sheet10"?

Any help will be greatly appreciated as the Boss is getting impatient.
Thankyou
Mike


"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Double Click 'This Workbook' and paste this in
on the right.

Change MySheet to the name of the sheet you want to copy to data to
Change MyColumn to the column where you will enter the quantity. Every time
you enter a greater than Zero entry in MyColumn it will copy the entire row
the MySheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MySheet = "Sheet2"
MyColumn = "D:D"
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


Mike


"Skeletor" wrote:

I have a series of worksheets that contain different product lines. When I
type in an amount in the "Quantity" column in each worksheet, I would like
that row to be inserted into a new worksheet automatically.

The problem is; I would like the new worksheet to contain only the rows from
each worksheet that have a "Quantity" value 0. The worksheet must fill from
the top row down and each new entry must be inserted in the next blank row
down the page. That way, I am printing a sheet that only contains the
selected items.

Any help will be greatly appreciated. Thankyou

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

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