Solved: Point me in the right direction please

[Aussiebear]

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.

[vba]

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

[/vba]

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

[lucas]

Haven't looked at this too closely yet but why do you have this code in the Thisworkbook module?
[VBA]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$H$2" And Target = "Yes" Then

ActiveSheet.Range("A2:H2").Copy
Sheets("CompletedWork").Range("A" & lRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False

'Reset the values in the row just copied
ActiveSheet.Range("A2:H2").ClearContents
End If
End Sub
[/VBA]

[Aussiebear]

Where should it be?

[lucas]

Hey Aussie,
I got this working but I had to delete your "Completed Work" sheet. I then copied the Outstanding work sheet and renamed it to Completed work and it works fine now. Seems formatting must be identical for this to work.

[Aussiebear]

Thank you Steve. Will try this at work to see if this meets their needs. It gets me out of some considerable trouble with work.

[mdmackillop]

[VBA]With Sheets("Completed Work")
.Range(.Cells(lRow, 1), .Cells(lRow, 8).PasteSpecial xlValues
End With
[/VBA]

Why go to the trouble of setting the destination range, when the top left cell will suffice? If you change your copy size, then the paste may fail.
Regards
MD

[Aussiebear]

Why go to the trouble of setting the destination range, when the top left cell will suffice? If you change your copy size, then the paste may fail.
Regards
MD

Malcolm, its me your talking to. No double speak please. You need to bear in mind that I'm "mentally", and "age" challenged.

What I took to work to day, refused point blank to work. Mind you I left work red faced and super frustrated.... I need a drink.


BTW. Keep them coming....

OKay whiz kids, if I shorten the size of a sheet ( anything right of Column I and anything below Row 500 do I cause the system to hyperventilate?

Ted

[geekgirlau]

Why go to the trouble of setting the destination range, when the top left cell will suffice? If you change your copy size, then the paste may fail.
Regards
MD

What Malcolm is talking about is how paste works in Excel. Try it manually: copy a range of cells, then select a single cell in another location and paste. You don't need to select the same number of cells as you copied - you just select the top left cell. You are much more likely to generate an error if you try to select the correct number of cells prior to pasting.

[mdmackillop]

Thanks GG

[Aussiebear]

Thanks GG, my problem is that I'm so tired form the long consistant hours at work that I'm mentally stuffed. I'm averaging 4 hours sleep a night and its becoming a struggle to survive. Could someone plese tell me in very plain language, the who what when and where for the spreadsheet I'm working on.

I don't care if you break it down to the A is for apple, b for bat, c for cat style. I am so tired anything looks good right now.

Ted

[Aussiebear]

Okay slept on this. I understand that which Malcolm is saying,