copy cell a1 to 1 of 2 wrkshts depending on the value of other cel

[jcurless]

Okay, First I want to say I am new to this so you'll have to bare with me...
I am using Excel 2003

What I have:
Inventory tracking Work book with 5 work sheets. (Old in, redeployment,
disposal, device info, and location) the last 2 are tables used only for
vlookups.
The main worksheet is “old in†and has 6 column’s
(b,c,e,f,g,h) (Others are hidden for later use if needed)
In Column H the user types yes or no to answer a question:
"is the device redeployable"

What I need:
If the text in work sheet “old in†cell H7 = YES
I need to copy the text listed in work sheet “old in†cell F7 to work sheet
“Redeployment†cell A4.
If the text in work sheet “old in†cell H7 = NO
I need to copy the text listed in work sheet “old in†cell F7 to work sheet
“Disposal†cell A4.
I want this to happen every time a cell in Column H is updated.
I currently have code that will auto populate the date in column B when
Column E is updated. I would need to ensure that the code doesnt effect that
action. Here is the code I have for that...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
With Target(1, -2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub

I have posted this on other sites but no one can figure out how to help!
If anyone would like to try to tacle this with me please do so!!
I can send a copy of the file if that would help!

 

[JLGWhiz]

This modifies your current worksheet change code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
With Target(1, -2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
If Target = Intersect(Target, Range("H3:H" & Cells(Rows.Count,
8).End(xlUp).Row) Then
If UCase(Range("H7")) = "YES" Then
Sheets("Old in").Range("F7").Copy
Sheets("Redeployment").Range("A4")
ElseIf UCase(Range("H7")) = "NO" Then
Sheets("Old in").Range("F7").Copy Sheets("Disposal").Range("A4")
End If
End If

End Sub

I did not test it, so give it a test run before permanently deleting the old
code. You cannot have two separate worksheet_change macros on the same
sheet, so you will neet to comment out (put apostrophes in front of code
lines) the old code while you test the new one.

 

[jcurless]

I tried it but here is the error it returned:
Run-time error '91':
Object variable or With block vriable not set

I clicked Debug and this is the line it highlighted:
If Target = Intersect(Target, Range("H3:H" & Cells(Rows.Count,
8).End(xlUp).Row)) Then

 

[JLGWhiz]

I always have a problem with that. After reading your post again
I believe this will work fine:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
With Target(1, -2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
If Target = Range("H7") Then
If UCase(Range("H7")) = "YES" Then
Sheets("Old in").Range("F7").Copy
Sheets("Redeployment").Range("A4")
ElseIf UCase(Range("H7")) = "NO" Then
Sheets("Old in").Range("F7").Copy Sheets("Disposal").Range("A4")
End If
End If

End Sub