Jake Peralta
New Member
- Joined
- Jun 9, 2020
- Messages
- 9
- Office Version
- 2010
- Platform
- Windows
Hi all,
Sorry for the lengthy original post.. I've been looking through the named range loop threads here and elsewhere but haven't been able to find what I'm after. I'm working on a large project which is principally driven by a worksheet change event, whereby each event triggers a copy and paste of formulas from various named ranges to various other named ranges (one by one at present i.e. Range("Copy59").Copy Range("DATA59").PasteSpecial xlPasteFormulas). As of this morning I was only dealing with 60ish named ranges (which was still kind of slow, maybe 30sec or so to complete the change event), but the scope of the project has blown out massively today and I'm now being asked to deal with over 1,600 named ranges (yes, 1,600, i didn't sausage finger an extra zero onto 160 by accident!) and given there's 3 variations on the event change it works out to be over 10,000 lines of code with how I've got it written at the moment and I'm getting a "Procedure is too large" error (exporting the cls file comes in at 110kb and I've read 64kb is the max). When I reduce it to 600 named ranges as a test I get it down to 48kb but it still takes over 5min for the change event to complete which is unacceptable.
Is there any way I can list all the names of the named ranges for where the formulas will be copied from in one column, and all the names of the corresponding destination named ranges in the adjacent column (perhaps it is useful to give each of these lists a named range of their own?) and then produce some code that copies all of the data at once rather than having thousands of lines of code that brings things grinding to a halt? e.g. I'd have a column with all the ranges to be copied that would look like Copy59 Copy60 Copy61 (say we give this a named range of its own "AllMySourceNamedRanges") and then a column with DATA59 DATA60 DATA61 which we can give a named range as well called "AllMyDestinationNamedRanges". From there, ideally what I'm after is Range("AllMySourceNamedRanges").Copy Range("AllMyDestinationNamedRanges").PasteSpecial xlPasteFormulas such that they're all copied and pasted in one go rather than having a copy and a paste line of code for each named range across all three change event scenarios!
Just to clarify, whilst I can put the names of the source and destination named ranges into columns side by side (or even the source named ranges themselves with the formulas), the actual destination cells where the formulas will be copied to can't be all in a single row or column, as they're placed in a logical way on the worksheet for users to make selections. In addition, I also want to try to keep everything on one page so I'm trying to avoid splitting things up over different sheets and/or having to create multiple procedures. Any help would be greatly appreciated folks!
Sorry for the lengthy original post.. I've been looking through the named range loop threads here and elsewhere but haven't been able to find what I'm after. I'm working on a large project which is principally driven by a worksheet change event, whereby each event triggers a copy and paste of formulas from various named ranges to various other named ranges (one by one at present i.e. Range("Copy59").Copy Range("DATA59").PasteSpecial xlPasteFormulas). As of this morning I was only dealing with 60ish named ranges (which was still kind of slow, maybe 30sec or so to complete the change event), but the scope of the project has blown out massively today and I'm now being asked to deal with over 1,600 named ranges (yes, 1,600, i didn't sausage finger an extra zero onto 160 by accident!) and given there's 3 variations on the event change it works out to be over 10,000 lines of code with how I've got it written at the moment and I'm getting a "Procedure is too large" error (exporting the cls file comes in at 110kb and I've read 64kb is the max). When I reduce it to 600 named ranges as a test I get it down to 48kb but it still takes over 5min for the change event to complete which is unacceptable.
Is there any way I can list all the names of the named ranges for where the formulas will be copied from in one column, and all the names of the corresponding destination named ranges in the adjacent column (perhaps it is useful to give each of these lists a named range of their own?) and then produce some code that copies all of the data at once rather than having thousands of lines of code that brings things grinding to a halt? e.g. I'd have a column with all the ranges to be copied that would look like Copy59 Copy60 Copy61 (say we give this a named range of its own "AllMySourceNamedRanges") and then a column with DATA59 DATA60 DATA61 which we can give a named range as well called "AllMyDestinationNamedRanges". From there, ideally what I'm after is Range("AllMySourceNamedRanges").Copy Range("AllMyDestinationNamedRanges").PasteSpecial xlPasteFormulas such that they're all copied and pasted in one go rather than having a copy and a paste line of code for each named range across all three change event scenarios!
Just to clarify, whilst I can put the names of the source and destination named ranges into columns side by side (or even the source named ranges themselves with the formulas), the actual destination cells where the formulas will be copied to can't be all in a single row or column, as they're placed in a logical way on the worksheet for users to make selections. In addition, I also want to try to keep everything on one page so I'm trying to avoid splitting things up over different sheets and/or having to create multiple procedures. Any help would be greatly appreciated folks!