We help IT Professionals succeed at work.

Overwrite existing values in a Master file when master file is updated or opened

166 Views
Last Modified: 2017-03-19
Q: I would like overwrite existing values when I update or open the master file. I am copying all Data files in a folder to a master file. To avoid duplicates I would like overwrite existing data in the master file.

Sample

Thank you.

Regards,


Adam
Master.xlsm
Slave1.xlsx
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Adam,

I wouldn't recommend to update as it opens. You might not have updated the Slave file, accidentally if you open Master, it will get over written.
Secondly over written means, you want to clear previous records and update with new ones or you want to copy new record and then delete duplicate values?

Author

Commented:
1. You are correct. updating as it opens is tricky.

2. If the user adds new data in the data file it should added to the master file when I click update master file. I don't know which solution is better and don't understand the difference.

In daily routine the user adds new records to her/his data file. Task is: New records should added to the master file. Old records should be saved in the master too.

What is the better solution? overwrite or just add new records to avoid duplicates.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
OK got it.

You need to append new records after old records, better not to clear old records then.

Let me work on it.

Author

Commented:
Thank you

Author

Commented:
For your information to avoid wasting your time and missunderstanding here  the daily routine:

In a Folder I have 30 Data files. Each User has a Data file ( Data1, Data2, etc.)
In the same Folder I have the master file where I collect/copy all data from the files of the users.

The table structure is in all Data Files the same.

Thanks you.

Regards,

Adam.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Shums, many thanks for your very fast and uncomplicated help. It is simple perfect and easy to customize.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I am glad that it worked in 1st attempt without any error. You'r are always welcome Adam.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.