Aussiebear
07-19-2006, 03:35 AM
I have a simple spreadsheet to record the Electrical work issues (jobs) for the contractor who is hard to get to complete jobs. The concept here is enable users to enter details of job requests into the spreadsheet such as Raised Date, Electrical Work issue, Priority rating, Discussed (Y/N),Quote required (Y/N), Action Date & Completed (Y/N). The object here is to have the jobs listed, and when completed enter a "Yes value in the "H" Column, which then clears the row from the "Outstanding Work" sheet to the "Completed Work" sheet.
Well that's the theory...
Anyhow I plagurised some code from another spreadsheet and came up with this.
Private Sub Worksheet_Change(By Val Target As Range)
' Written 19/07/06 by Ted Eggleston
'Copy and Paste "Yes" row to Completed Work Sheet
If Target.Cells.Count >1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 8 and Target.Row >1 And Target = "Yes" Then
lRow = Sheets("Completed Work").Cells(Row.Count, 1).End (xlUp).Offset_ (1).Row
With ActiveSheet
.Range(.Cells(Target.Row, 1), .Cells(Target.Row, 8))).Copy
With Sheets("Completed Work")
.Range(.Cells(lRow, 1), .Cells(lRow, 8).PasteSpecial xlValues
End With
' Reset default values for the row just copied
cRow = Target.Row
.Range("A" &cRow & ":H" & cRow). ClearContents
End With
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Am I even in the same ball park as the concept? When the value in cell "H" changes from No to Yes, I would like that row to be copied to the second sheet and ... then the initial row copied to be cleared. On trying this section of code I am greeted with a procedure to create a macro, which I wanted to call "Completed_Work". And then I came really unstuck...
It wants me to fill in some more code. What goes in here?
Back on the main sheet I now have an empty row. Can I move the rows up to fill the gap without having to use the Cut & Paste mode?
Ted
Well that's the theory...
Anyhow I plagurised some code from another spreadsheet and came up with this.
Private Sub Worksheet_Change(By Val Target As Range)
' Written 19/07/06 by Ted Eggleston
'Copy and Paste "Yes" row to Completed Work Sheet
If Target.Cells.Count >1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 8 and Target.Row >1 And Target = "Yes" Then
lRow = Sheets("Completed Work").Cells(Row.Count, 1).End (xlUp).Offset_ (1).Row
With ActiveSheet
.Range(.Cells(Target.Row, 1), .Cells(Target.Row, 8))).Copy
With Sheets("Completed Work")
.Range(.Cells(lRow, 1), .Cells(lRow, 8).PasteSpecial xlValues
End With
' Reset default values for the row just copied
cRow = Target.Row
.Range("A" &cRow & ":H" & cRow). ClearContents
End With
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Am I even in the same ball park as the concept? When the value in cell "H" changes from No to Yes, I would like that row to be copied to the second sheet and ... then the initial row copied to be cleared. On trying this section of code I am greeted with a procedure to create a macro, which I wanted to call "Completed_Work". And then I came really unstuck...
It wants me to fill in some more code. What goes in here?
Back on the main sheet I now have an empty row. Can I move the rows up to fill the gap without having to use the Cut & Paste mode?
Ted