Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Synchronizing/mirroing cell, rows and colums by Range names

Good morning dear Helpers,
I asked precedently on this forum to synchronize in mirror tables from several sheets and the response was great and very efficient.
I then tried from the result to change the ranges of the VBA code by group names so that if "Team" users add a rows in one of the tables, it automatically adds it to the main one, and vice versa.
It didn't work, it doesn't reconize the range.
For exemple, if I need to addor even delete a row or a column, it would auto-do the same in the linked table...
Erro 1004...

Edit :
Why would a row or column be needed/deleted?
I'm working on a budget divided into several departments.
Rows represent team members (wuth different salaries), and sometimes we need to add or remove some.
I first put an automatism (with a button) which automatically hide the empty lines but then it's difficult to add them (or with another button)...

Where in the table would the row or column be added/deleted?
Anywhere beetween the first and the last of each block...

Would it be acceptable to have buttons on the worksheet (Add / Delete)?
Yes indeed. Tut I didn't find a clear solution with an effect on both sheets.

Thank you very very much for your help and your time !


Hello Tdy,
Glad to see you gave it a try to solve this on your own. Too bad it didn't work.
Run Time error 1004 typically ocurrs when the vba code wants to select a cell or range not on the active sheet.
Firstly, I didn't notice until now that you are a Mac user. I am a Windows (Office 2007) user. There are a few subtle differences between the Windows and Mac versions of Office so I'm not sure I will be able to solve this for you.
Second, you haven't provided the additional details (that I stated in your first post) which are needed to provide a useful solution. >> Why would a row or column be needed/deleted? Where in the table would the row or column be added/deleted? Would it be acceptable to have buttons on the worksheet (Add / Delete)? These, and maybe other details, are needed so there is no guessing how you want the add/delete to function.
And let's not forget, the Worksheet_Change event code I originally wrote will also need to be modified because changing the "table" size will affect the Offset values.
If/after you provide these details, I might take a shot at finding a solution if I have time.
Important: in your attached file, the macro code is not correct/functional and is not my original code. I would appreciate it if you would remove my name from the top of each macro so others are not misled.

Cheers   :-)
WillieD24 (rep: 210) Feb 8, '23 at 11:19 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login