| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell a1 to 1 of 2 wrkshts depending on the value of other cel
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 columns (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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell a1 to 1 of 2 wrkshts depending on the value of other cel
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" wrote: 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 columns (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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell a1 to 1 of 2 wrkshts depending on the value of other
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" wrote: 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" wrote: 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 columns (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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy cell a1 to 1 of 2 wrkshts depending on the value of other
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 "jcurless" wrote: 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" wrote: 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" wrote: 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 columns (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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy cell contents depending on font colour | Excel Worksheet Functions | |||
| How do I copy a row depending on the content of a cell in the row | Excel Worksheet Functions | |||
| Copy content of cell to another depending on value of third cell(between worksheets) | Excel Worksheet Functions | |||
| Split into 2 wrkshts, now function doesn't work | Excel Worksheet Functions | |||
| Automated cell copy depending on cell content? | Excel Programming | |||
Linear Mode
