Update workbook 2 when I delete content from workbook1

[cgkmal]

Hi to all,

I have workbook 1 and workbook 2, and I need a way to update workbook2 automatically when I do changes in workbook 1, I mean, when I delete range content (e.g. C7 or row 5, etc), the corresponding range be deleted in workbook 2 automatically.

The content to delete is located in different sheet names in both workbook, and in different tables structure, I mean C7 or row 5 is in SheetX in WBook1 but identical C7 content or row 5 content is located in SheetABC in Wbook2 and in different positions, C7 could be located in H20, row 5 could be row17.

I hope make sense, may somebody suggest me how can I get this?

Any help would be very appreciated.

Regards

[Richard Buttrey]

Hi,

This is somewhat difficult to answer without seeing your actual workbooks. It's always easier to help if we can see a request in context. But before we go further down this route are you sure that this is the best way of achieving your ultimate goal. A few questions first.

1. Do you really need two workbooks. If you're trying to keep two workbooks with similar contents, what's the purpose of the second workbook?

2. If you really do need two workbooks, is it not an option to recreate a new second workbook each time you change workbook 1. This may be much easier than trying to match similar cells/ranges and then make changes in wb2.

Regards

[cgkmal]

Hi,

This is somewhat difficult to answer without seeing your actual workbooks. It's always easier to help if we can see a request in context. But before we go further down this route are you sure that this is the best way of achieving your ultimate goal. A few questions first.

1. Do you really need two workbooks. If you're trying to keep two workbooks with similar contents, what's the purpose of the second workbook?

2. If you really do need two workbooks, is it not an option to recreate a new second workbook each time you change workbook 1. This may be much easier than trying to match similar cells/ranges and then make changes in wb2.

Regards

Hi Richard,

Thanks for your reply.

The first wbook contains changes that many users do when access to wbook 1 in a share network. Wbook 2 is a summary of all data entered in wbook 1 by those users. Actually the books content is very different in layout and in presentation. Both are established formats used in the job. I was thinking to join both workbook content in a unique one, but for now is not my decision because depends of some people opinion.

For now I think to explain better, I only need a way, and Event that let me know which content is deleted (which cell in which sheet containing which content) in order to do a search in second workbook and delete that content.

I use the below code to update the wbook2 when users enter values in workbook 1

Please Login or Register  to view this content.

Now I need to execute other code to delete in workbook 2 the content that is deleted in any cell of workbook 1.

I only need to know or store the content that will be deleted before disappear from display.

I hope make sense.

Many thanks for your help so far

Regards

[Richard Buttrey]

Hi,

Upload your workbooks so that we can see exactly what you mean. Add some notes explaining that if I delete this range in file 1 sheet X, I want that range in file 2 sheet Y to be deleted...etc..

Regards

[cgkmal]

Hi Richard,

I attached Wbook1.xlsx and Wbook2.xlsx. The issue is:

If I press delete key over Item "AS-469" that is located in A4 of Wbook1.xlsx, I want to detect when "Delete" key is pressed and in which cell to know the content of that cell, in order to search the same value in Wbook2 and delete completely its row in Wbook 2.

Once the row corresponding to value "AS-469" in wbook2 has been deleted, delete row 4 in Wbook1.

As I said before, I have solved the part that updates Wbook2 when I enter values in Wbook1, now I only need to update (delete content) Wbook2 when a value is deleted in Wbook1.

Please Login or Register  to view this content.

Many thanks in advance for any help.

Regards.

[Richard Buttrey]

Hi,

Does the attached help? In the Workbook Sheet change event of Book1. - which will now be a .xlsm file of course.

Please Login or Register  to view this content.

[cgkmal]

Hi,

Does the attached help? In the Workbook Sheet change event of Book1. - which will now be a .xlsm file of course.

Hi Richard,

Yes, It helps a lot. That part of the application.undo does the trick and solved many problems I had, nice idea!!!.

I think this is what I was looking for, I works great.

Many thanks really.

Best regards.