Dynamic cell references

[lb6067]

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:

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!

 

[Trevor_S]

Give each cell that you want to refer to a name (in the workbook). Then refer to the named range in the macro. For example, if you set up a named range for cell Z4 called Ref1, then in your macro replace Range("Z4") with Range("Ref1").

 

[lb6067]

Give each cell that you want to refer to a name (in the workbook). Then refer to the named range in the macro. For example, if you set up a named range for cell Z4 called Ref1, then in your macro replace Range("Z4") with Range("Ref1").

Thanks for the response. I tried it and it didn't work. I created names for every applicable cell and placed them in the code. Based on the code example I used above, is there anything else I would need to change? Thanks!

 

[Trevor_S]

Can't see where the problem would be ... is there an actual error message, or is it just not doing what you would expect?

 

[lb6067]

Can't see where the problem would be ... is there an actual error message, or is it just not doing what you would expect?

Thanks Trevor_S.

There is no error with the code as I had pasted below. The cells update as they are meant to. The error comes when I replace the cell numbers with named ranges. For instance, in the example below, if I change Case "B5" to Case "firstmonth_visitors" and Sheets("Overview").Range("Z4") = Target to Sheets("Overview").Range("firstmonth_visitors_2") = Target (and all the others to match the named ranges I have created, it doesn't work. I don't get an error, there is just no updating.

 

[Trevor_S]

Try changing the Case... lines as follows:

    Case Range("firstmonth_visitors").Address 'First month visitors

That way you're comparing an address with an address, rather than an address with a range name.

 

[lb6067]

Try changing the Case... lines as follows:

    Case Range("firstmonth_visitors").Address 'First month visitors

That way you're comparing an address with an address, rather than an address with a range name.

You are a genius!!! Worked perfectly! Thanks so much!