Error when "mirroring" named ranges between worksheets

[fishermanryan]

Hello,
I have several named ranges on a worksheet, which are intended to "mirror" another range in another sheet so that if info is typed, deleted, or copied into a cell in sheet 1, it will automatically appear in the corresponding range in sheet 2. The code below references what I've put in the front sheet.

When I open the workbook and change a cell value in sheet 1 (range MBO_FFV_1), I get an error "Run time error 424, Object required". The debugger takes me to the line " [MBO_FFV_2].Value = [MBO_FFV_1].Value".

Of course, the same code is copied in sheet 2 with the appropriate range names switched. The mirroring will work if I type in sheet 2 without any error, but if I type in sheet 1 i get the error.

I can't figure out how to fix the code so the "mirroring" will work. Any suggestions?

Please Login or Register  to view this content.

[watersev]

hi fishermanryan, if Sheet1 has MBO_FFV_1 named range and Sheet2 MBO_FFV_2 named range, this works as expected

Sheet1 code:

Please Login or Register  to view this content.

Sheet2 code

Please Login or Register  to view this content.

[fishermanryan]

I agree, the code should be working. However, I'm still having issues.

If I edit a cell value on Sheet2, the corresponding value is changed on Sheet 1. However, if I edit a cell value on Sheet1, it gives me the mentioned error. I feel pretty confident the code is correct; is there something else that could be creating the error?

[watersev]

can you post a workbook with the coding for testing?

[fishermanryan]

A sample workbook is attached. Any suggestions are appreciated! Book1.xlsm

[watersev]

please check attachment, the codes were missing sheet reference for nonactive sheet named range

[fishermanryan]

Curious...
Your adjusted sheet didn't work on my computer. I tried changing cell values on one sheet or the other and they weren't mirrored (macros are enabled). As a side note, other named ranges in my workbook don't need the sheet specified when trying to mirror, so I'm not sure why this one would be any different.

Could there be some setting on my computer that I'm not aware of that could be blocking the macro?

Or, could the fact that I've been making lots of edits and changes to this workbook using named range "MBO_FFV_1" have created some issue?

[watersev]

try to run this code first:

Please Login or Register  to view this content.