Procedure Too Large Error...

Hello,

I am trying to use VBA to write a code to make it possible to link two cells together from different worksheets, but it needs to be linked both ways. When I input a number/date/word, I need it to update it in a particular cell on another worksheet and vice versa. For example, I need cells in column A (Transaction Number) in SHEET1 to be linked to cells in column D (Transaction Number) in SHEET2 [and vice versa]. I am not sure if you can link all cells in one column to all cells in another column, but I found a macro that I could copy the code and change the cell # to cover the cells needed. The problem I am running into, however, is that I can get the code to cover A2 all the way up to A120, but if I go any bigger then I get an error saying that the procedure is too large. I tried to make it shorter by using the same name twice (which I get the error "Ambiguous Name Detected." I know you cannot have the same name twice, but if I change it then the code does not work. For example, the name was: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) and the name I changed the second one to was: Private Sub Workbook_SheetChange1(ByVal Sh As Object, ByVal Target As Range). I am not sure how to either change the name so that it will work, or shorten the macro. I am sure there is a more efficient way to write this code, but I am very new to this. Any help would be much appreciated!

Attached is the excel workbook that I am using. To view the code, right-click SHEET1 and then click THIS WORKBOOK. The code I am using here is the one where I get the "Procedure Too Large" Error: [*Code courtesy of Dave Hawley from a different forum website that I found, where he posted it in response to someone's question]

PO & SO TRACKING SHEET.xlsmPO & SO TRACKING SHEET.xlsm

Olly,

Thank you so much! This works perfectly!!! Is it possible to use this code to link more cells in different columns. For example, link column B in SHEET1 to column E in SHEET2, column D in SHEET1 to column A in SHEET2, and column E in SHEET1 to column A in SHEET2 [plus some more combinations like these].

Nevermind I figured it out! In case anyone comes across this looking to do something similar to what I am doing, below is how I was able to link more columns together using Olly's code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Sh.CodeName = "Sheet1" And Not Application.Intersect(Target, Range("A2:A10000")) Is Nothing Then
Sheet2.Range("D" & Target.Row).Value = Target.Value
ElseIf Sh.CodeName = "Sheet2" And Not Application.Intersect(Target, Range("D2:D10000")) Is Nothing Then
Sheet1.Range("A" & Target.Row).Value = Target.Value
End If
Application.EnableEvents = True
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Sh.CodeName = "Sheet1" And Not Application.Intersect(Target, Range("B2:B10000")) Is Nothing Then
Sheet2.Range("E" & Target.Row).Value = Target.Value
ElseIf Sh.CodeName = "Sheet2" And Not Application.Intersect(Target, Range("E2:E10000")) Is Nothing Then
Sheet1.Range("B" & Target.Row).Value = Target.Value
End If
Application.EnableEvents = True
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Sh.CodeName = "Sheet1" And Not Application.Intersect(Target, Range("D2:D10000")) Is Nothing Then
Sheet2.Range("A" & Target.Row).Value = Target.Value
ElseIf Sh.CodeName = "Sheet2" And Not Application.Intersect(Target, Range("A2:A10000")) Is Nothing Then
Sheet1.Range("D" & Target.Row).Value = Target.Value
End If
Application.EnableEvents = True
End Sub


THANK YOU AGAIN OLLY!

Yes it does I really appreciate your help with this! I've been trying to figure it out for days now..

The original code you gave me was working great, but then I added more elseif to link more columns together and now I can't get it to work. I'm sure it's something I did to the code, but I haven't been able to fix it. Any suggestions?

I'm new to this so I hope I did the code tags correctly... It's not working in terms of not functioning as needed. The linked cells are not functioning as linked cells. I may have just messed up the code, but here is what I put into the THIS WORKBOOK in VBA:

Please Login or Register  to view this content.

It works now!!!!!! Thank you so much