VBA Creating Name Range after Pasting Data Over Current Named Range

  • Hi guys, I hope you’re doing well, I need a big favor from you as it’s going to be complicated code here

    I need a code to check if named range is there or not by checking the value of cell M3 in sheet Home

    If it exists, to go to the sheet where this named range is there and select it

    If it doesn’t exist, so, we need to create it by:

    • Going to the sheet name displayed in cell M1 in sheet Home
    • Checking the next available empty cell in column C and name it with the needed range name displayed in cell M3 in sheet Home


    However, the coming is the hard part

    • That later on, there’ll be data pasted over a number of cells including the one represents the named range and I need the named range to be redefined to include this new area
    • It’s expected that the new area will start from the direct next left cell from the named range cell down to the last non-empty cell in column C expanding till the right to Column O


    Is it doable?

    I’ve attached a sample file to facilitate this hard request, thank you in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!