Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy rows with values 0 to another worksheet automatica
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy rows with values 0 to another worksheet automatica
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy rows with values 0 to another worksheet automa
Thankyou. I will try this tomorrow and let you know.
"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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy rows with values 0 to another worksheet automa
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to copy rows with values 0 to another worksheet automa
Change only the actual sheetname from "Sheet2"
MySheet = "Job List" Change only the column letter from "D:D" MyColumn = "F:F" Continue to use MySheet and MyColumn where Mike has typed those literal strings. Gord Dibben MS Excel MVP On Wed, 24 Sep 2008 00:53:01 -0700, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy (Formulas and Formatting ONLY) no values to other rows | Excel Discussion (Misc queries) | |||
Copy values in columns to rows | Excel Worksheet Functions | |||
copy rows to another worksheet | Excel Worksheet Functions | |||
how to add word to the front of every word in all rows automatica. | Excel Discussion (Misc queries) | |||
How to copy values in various rows automatically | Excel Discussion (Misc queries) |