I'm new to using VBA in Excel, so my apologies if I am not explaining this well.
I have created a complex spreadsheet with a number of worksheets. I have multiple instances (dozens) of cells that update each other in both directions, i.e. if data is entered in worksheet 1 cell B1, worksheet 2 cell B1 is automatically changed and same in the other direction. All of the references are currently default absolute. I need to make changes to the structure of the worksheets, i.e. add rows and columns, so the cell references will no longer be valid unless i manually change them, which will be a major effort. How can I make the references dynamic? I have read it is possible, but have yet to find a clear explanation of how it can be done. Here is an example of my code from one of my worksheets:
Thanks in advance for your help!
I have created a complex spreadsheet with a number of worksheets. I have multiple instances (dozens) of cells that update each other in both directions, i.e. if data is entered in worksheet 1 cell B1, worksheet 2 cell B1 is automatically changed and same in the other direction. All of the references are currently default absolute. I need to make changes to the structure of the worksheets, i.e. add rows and columns, so the cell references will no longer be valid unless i manually change them, which will be a major effort. How can I make the references dynamic? I have read it is possible, but have yet to find a clear explanation of how it can be done. Here is an example of my code from one of my worksheets:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "B5" 'First month visitors
Sheets("Overview").Range("Z4") = Target
Sheets("CFA - B2B").Range("B179") = Target
Sheets("Investor Highlight").Range("I10") = Target
Case "B6" '% MOM growth of visitors
Sheets("Overview").Range("Z5") = Target
Sheets("CFA - B2B").Range("B180") = Target
Sheets("Investor Highlight").Range("G7") = Target
Sheets("Investor Highlight").Range("I7") = Target
Case "B7" '% sign up for User ID
Sheets("Overview").Range("Z6") = Target
Sheets("CFA - B2B").Range("B181") = Target
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Thanks in advance for your help!
Last edited by a moderator: