| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy Rows with a cell value 0 from several worksheets to a new sh
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy rows with a cell value 0 from several worksheets to a new sh | Excel Worksheet Functions | |||
| how do i copy a cell in worksheets 10 to the other 9 worksheets | New Users to Excel | |||
| Copy content of cell to another depending on value of third cell(between worksheets) | Excel Worksheet Functions | |||
| macro to copy multiple rows to separate worksheets | Excel Worksheet Functions | |||
| how do you copy the same cell reference on different worksheets | Excel Worksheet Functions | |||
Threaded Mode
