Using relative references in macros

[Moonbeam111]

Hi all. I have a worksheet with alot of macros in it. I want to insert a column at Column B but, if I do, I know this will make my ranges incorrect in my macros by 1 column. Is there a quick and/or easy way to fix this? (i.e. without having to manually change all my ranges in my worksheet)?

 

[Akuini]

Put the range in a named range, then use the named range in the macro.

 

[Moonbeam111]

Hi Akuini. I'm still a little bit confused. Here's a sample of a code from one of my macros. it wont works as intended anymore since the ranges are referring to the wrong column.

For Each pic In ActiveSheet.Pictures
        If Not Intersect(pic.TopLeftCell, Range("C21:C22")) Is Nothing Then
            pic.Delete
        End If
    Next pic

    Range("C153:D153").Copy
    Range("C21").Select
    ActiveSheet.Paste
    
    Range("E159:E160").Copy
    Range("E26").Select
    ActiveSheet.Paste
    
    Range("C155:D160").Copy
    Range("C22").Select
    ActiveSheet.Paste

If I named a range "myRange". How would I refer all these ranges to the right place?

ActiveWorkbook.Names.Add "myRange", "?" 'what should I put in the question mark

 

[Akuini]

Try it on a clean sheet first:
Say range "C153:D153", then:

ActiveWorkbook.Names.Add "myRange", Range("C153:D153")

after running that code you can see "myRange" in Name Manager window refer to "C153:D153"
Now, select col A then insert a column, you will see myRange now refers to "D153:E153". So it shifts 1 column.

Now in your actual sheet, run that code.
Then in your code, replace this part:

Range("C153:D153").Copy

with:

Range("MyRange").Copy

You need to create different named ranges for each range in your code.

 

[Moonbeam111]

Thank you. You saved me alot of time and effort.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.
Also, you can simplify your code for copy-paste. Like this part:

    Range("C153:D153").Copy
    Range("C21").Select
    ActiveSheet.Paste

can be written like this:

Range("C153:D153").Copy Range("C21")

and of course in your new code you need to change the range address to the named range.

 

[Moonbeam111]

Ok i'll do that. I know that select is frowned upon being used in the VBA community.

Thanks for the bonus tip.

 

[Akuini]

You're welcome.