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,

MySheet is the name of the sheet you want the data copied to and you use the
name you see on the worksheet tab.

You don't change the word MyColumn that's a variable used in the code.
In your original post you said when you enter a quantity greater tan zero
you want the entire row copying to a new sheet but didn't tell us where you
entered it so I guessed at column D. The syntax "D:D" refers to the entire
column so if you enter the quantity in column F you change this to "F:F".

I don't understand your impatience. You had an answer yesterday within 30
minutes of posting your question and that's not a bad response.

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

Mike

Thankyou for your reply. I am not impatient. But the owner of the business
where I work is. He wants to start saving paper "yesterday".

Thankyou for your answer, I will give it a shot again tomorrow.

"Mike H" wrote:

Hi,

MySheet is the name of the sheet you want the data copied to and you use the
name you see on the worksheet tab.

You don't change the word MyColumn that's a variable used in the code.
In your original post you said when you enter a quantity greater tan zero
you want the entire row copying to a new sheet but didn't tell us where you
entered it so I guessed at column D. The syntax "D:D" refers to the entire
column so if you enter the quantity in column F you change this to "F:F".

I don't understand your impatience. You had an answer yesterday within 30
minutes of posting your question and that's not a bad response.

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

Mike H,

I have tried this method, and for some reason, it doesnt work. What am I
doing wrong? Please help.
Here is what I typed in;

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
JobList = "Sheet10"
MyColumn = "F:F"
If Target.Cells.Count 1 Or IsEmpty(Target) Or ActiveSheet.Name = (Joblist)
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(JobList).Cells(Rows.Count, "A").End(xlUp).Row
Sheets(JobList).Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End If
End Sub

Thankyou again, Mike


"Mike H" wrote:

Hi,

MySheet is the name of the sheet you want the data copied to and you use the
name you see on the worksheet tab.

You don't change the word MyColumn that's a variable used in the code.
In your original post you said when you enter a quantity greater tan zero
you want the entire row copying to a new sheet but didn't tell us where you
entered it so I guessed at column D. The syntax "D:D" refers to the entire
column so if you enter the quantity in column F you change this to "F:F".

I don't understand your impatience. You had an answer yesterday within 30
minutes of posting your question and that's not a bad response.

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