Home |
Search |
Today's Posts |
#1
|
|||
|
|||
data entry from two cells across worksheets
I'm wanting to have cells take the value of the last one updated across
worksheets. I've found the code that accomplishs this within one worksheet, but not across two. Could anyone make ammendments to the following code for me? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Range("A1").Value = Range("A2").Value ElseIf Target.Address = "$A$1" Then Range("A2").Value = Range("A1").Value End If End Sub Thanks greatly Steve |
#2
|
|||
|
|||
Try this. Might work
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Range("A1").Value = Range("A2").Value ElseIf Target.Address = "$A$1" Then Range("A2").Value = Range("A1").Value End If Sheet2.Range("A1").Value = Sheet1.Range("A1").Value Sheet2.Range("A2").Value = Sheet1.Range("A2").Value End Sub Similarly for other sheets as well "sp-googling" wrote: I'm wanting to have cells take the value of the last one updated across worksheets. I've found the code that accomplishs this within one worksheet, but not across two. Could anyone make ammendments to the following code for me? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Range("A1").Value = Range("A2").Value ElseIf Target.Address = "$A$1" Then Range("A2").Value = Range("A1").Value End If End Sub Thanks greatly Steve |
#3
|
|||
|
|||
I think I'm missing something...
What I tried to take from your advise resulted in this mess: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "Periferals!$A$20" Then Monitors.Range("A20").Value = Periferals.Range("A20").Value ElseIf Target.Address = "Monitors!$A$20" Then Periferals.Range("A20").Value = Monitors.Range("A20").Value End If End Sub which I'm putting in "ThisWorkbook" and I'm still not getting anything |
#4
|
|||
|
|||
sorry guys and gals, is this one tougher than I thought?
|
#5
|
|||
|
|||
I'm not sure I understand what you really want...
If you make a change to A20 on Monitors or Periferals, you want that change to be echoed onto the other worksheet? If yes, then this worked ok for me (code is still behind the ThisWorkbook module): Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim OtherSheetName As String Dim myAddr As String 'only one cell at a time If Target.Cells.Count 1 Then Exit Sub End If myAddr = "A20" If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then Exit Sub End If OtherSheetName = "" Select Case LCase(Sh.Name) Case Is = "periferals": OtherSheetName = "monitors" Case Is = "monitors": OtherSheetName = "periferals" End Select If OtherSheetName = "" Then Exit Sub End If On Error GoTo errHandler: Application.EnableEvents = False Sh.Parent.Worksheets(OtherSheetName).Range(myAddr) .Value = Target.Value errHandler: Application.EnableEvents = True End Sub By the way, I think the correct spelling for Periferals is Peripherals. If you change the the worksheet name, remember to change the name in the code, too. sp-googling wrote: sorry guys and gals, is this one tougher than I thought? -- Dave Peterson |
#6
|
|||
|
|||
Excellent stuff Dave. What do I do if I want the two cells to be
different locations rather than just sheets? Peripherals!A20 and monitors!B4 for example. I know I should have asked the first time around, but you know how these things go... btw thanks for the proof reading too :-) |
#7
|
|||
|
|||
First, I think I'd take a slightly different approach. I think I'd use just one
worksheet for input and put a formula in the other: =monitors!b4 (in the peripherals worksheet). If you lock that cell and protect the sheet, it should be ok. But if you want... Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim myOtherCell As Range Dim myPerCell As Range Dim myMonCell As Range 'only one cell at a time If Target.Cells.Count 1 Then Exit Sub End If Set myPerCell = Worksheets("peripherals").Range("a20") Set myMonCell = Worksheets("monitors").Range("b4") Set myOtherCell = Nothing If Sh.Name = myPerCell.Parent.Name Then If Intersect(myPerCell, Target) Is Nothing Then Exit Sub Else Set myOtherCell = myMonCell End If ElseIf Sh.Name = myMonCell.Parent.Name Then If Intersect(myMonCell, Target) Is Nothing Then Exit Sub Else Set myOtherCell = myPerCell End If End If If myOtherCell Is Nothing Then Exit Sub End If On Error GoTo errHandler: Application.EnableEvents = False myOtherCell.Value = Target.Value errHandler: Application.EnableEvents = True End Sub sp-googling wrote: Excellent stuff Dave. What do I do if I want the two cells to be different locations rather than just sheets? Peripherals!A20 and monitors!B4 for example. I know I should have asked the first time around, but you know how these things go... btw thanks for the proof reading too :-) -- Dave Peterson |
#8
|
|||
|
|||
top notch Dave. I'd also use formulas - since I also don't know code -
but this is on a preexisting workbook with numerous formulas referencing the two cells and the solution you've offered will save the integrity of the data as well as several hours of changing the workbook. Your assistance has been very much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort data into 2 worksheets in excel vba? | Excel Worksheet Functions | |||
How to protect and unprotect 30 worksheets in a file every month . | Excel Worksheet Functions | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) | |||
stop excel from shifting cells up when a query returns no data | Excel Worksheet Functions |