How to select elect a range by clicking on a cell outside of that range?

[musicgold]

Hi,

I frequently need to copy a group of cells and paste it in a Word document. However before I copy the group, I need to make a small change in a cell in the group. So I have to select the range every time; it takes a few seconds and is tedious.

So is there a way so that I can click on a cell (say A1) and select a range B9:U27?

I did look at creating a dynamic range and clicking on it from the box on the top left, however I have a number of dynamic ranges and trying to find a particular range in the list is tedious.

Thanks

 

[Fluff]

It needs to go in the sheet module, rather then the ThisWorkbook module

 

[sykes]

Read my post, above - about EXACTLY what to do.

 

[NoSparks]

target.address = "$B$4"

 

[NoSparks]

and should be in the sheet module of the sheet containing that B4, not ThisWorkbook module

 

[musicgold]

Thanks folks. It works!