VBA PasteSpecial Named Range - Cannot change part of a merged cell
Hi,
I'm trying to paste special a named range in Excel 2007 but I get run-time error 1004: "Cannot change part of a merged cell".
An example of one of the parts of the named range would be cells E16&E17 merged but cell E16 contains the data so that is referenced in the named range.
Please Login or Register to view this content.
Any help would be appreciated.
Thanks,
M
Can you explain again as I have Set up a named range from E16:E30 and merged E16:E17 and the below code runs without error.
as doesPlease Login or Register to view this content.
andPlease Login or Register to view this content.
andPlease Login or Register to view this content.
andPlease Login or Register to view this content.
Please Login or Register to view this content.
If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved
Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
Thanks for the quick response! I am however having difficulty in running them, a lot of them fail on the .Copy function.
I've tested them all:
1. Pastes the same value to all cells, doesn't randomise
2. Error 1004 - Bugs on the .Copy function
3. As above
4. As above
5. Doesn't paste special the values, the formulas still exist
Not for me, I am afraid I don't get any of those. Please see the attached.
If your merged cells range covered cells that were partly outside the named range say in the attached instead I merged E16:F17 then you will get the run-time error 1004: "Cannot change part of a merged cell" message.
That is because only part of the range in in the merged cells (as the error states).
So in your case if E16 is the named range and E17 is outside the named range then you will get the error.
Then all you can really do is unmerge the cells then remerge them or as it is regularly stated "Avoid using merged cells when coding" (or ever).
Last edited by WasWodge; 06-22-2016 at 06:59 AM.
Okay so I've fixed the merged cell issue.
I'm now getting an error "That command cannot be used on multiple sections"
My named range covers several cells across the worksheet that aren't directly near each other.
Is there an alternative to the .Copy scenario?
Something like...
Please Login or Register to view this content.
Got it working in the end, thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks