Worksheet Change Macro with multiple Keycell ranges

[owstie]

Hi, I’m trying to make the most recent input change made to cells in a series of ranges automatically be copied and pasted elsewhere (which then it turn triggers a vlookup but that bit is fairly irrelevant).

I’ve managed to create a Worksheet Change macro but I get the “Run-time error ‘1004’: Method ‘Range’ of object ’_Worksheet’ failed” whenever I input one of the cells in the ranges.
It works fine if I only have 20 ranges rather than the 40 I’ve got in my coding(why is this?! – v frustrating!.
To get round this I’ve tried (unsuccessfully) experimenting with MultipleRanges and Union. The coding is below, please help. Cheers.

Please Login or Register  to view this content.

[wallyeye]

A limitation of the Range property, you can only have 256 characters. You can split it up into multiple groups:

Please Login or Register  to view this content.

I cut out some of the intermediate code as well, since you are pastingspecial to get target.value I just directly assigned target.value to range("x508").

[Leith Ross]

Hello owstie ,

Welcome to the Forum!

Using the Union method works for combining the ranges. In your macro I disable and enable the Application Events. This is important whenever you aare using Worksheet_Change or Worksheet_SelectionChange events. If you change or select a cell in theses events respectively then you will create a cascade event condition which will cause the stack to overflow. This will usually crash Excel.

It is rare in VBA that you will need to Select and object before performing an operation on it. Also, if you want to "copy" values, the easiest method is to assign the Value of source range to the destination range.

Please Login or Register  to view this content.

[owstie]

Thank you very much gents. Superb help. Looking forward to being part of this forum.