Copy data from one workbook sheet to another.

Hello all.

Was hoping someone here could help me out with a commandbutton that would copy data from one workbook1 sheet to a separate workbook2 sheet starting from A16:G16 on down. But copy in a way that it copies over data over were it doesn't overwrite the old data and also doesn't copy over duplicate row entries.  I have a workbook1 I have multiple tabs with all employees names. And in workbook2, they only have a tab with there name to enter data. So when they finish updating there info and they click the button it will automatically copy the new data over to worksheet1 in the correct worksheet name. Is this possible?
HEMIChallengerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
HEMIChallenger,

Yes, what you want is possible. You just have to upload sample workbooks so that I have the problem Workbook names and Worksheet names to work with to write up the VBA code.
0
HEMIChallengerAuthor Commented:
Hello
Thanks for replying. Attached examples of how the workbooks looks. In the Employee tracker they would have a commandbutton they could click when they finish updating there data and it would automatically copy the new date over to master tracker in there correct sheet name starting from A16:G16 on down. but it won't overwrite any data in the master tracker it would just copy every thing in the next available empty row. and it won't copy over any duplicate row entries (ticket). would it be possible to have it error if it fails or if it copies over it messages copy complete?
MASTER-TRACKER.xlsm
EMPLOYEE-TRACKER.xlsx
0
Harry LeeCommented:
HEMIChallenger,

Can you please take a look at the sample files? To see if they works?

I may need to know where exactly you save the Master-Tracker.xlsm

BTW, I have changed some formulas in the Master Tracker workbook. Instead of having a fixed range like B4:B600, I have change it to count the whole column $B:$B. This way, the count would work properly if some rows would be deleted for any reason.
EMPLOYEE-TRACKER.xlsm
MASTER-TRACKER.xlsm
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HEMIChallengerAuthor Commented:
Hello Sir,
Yes it works the first time you enter data. But after i save and close and reopen and  enter new data under the previous entries and save data it gives and error run time error 438. But the location of the master tracker will be on a server \\server\data\employee\trackers And the employees trackers will be saved in the same location but in a folder named after the employees \\server\data\employee\trackers\employees folder.
0
Harry LeeCommented:
HEMIChallenger,

What do you mean by "enter new data under the previous entires"?

I hope there is no misunderstanding. Is what you mean each employee tracker file will keeps appending?

I thought the employee tracker file will be reset to clean every time, and the Save Data command button moves the data to the Master Tracker; otherwise, there is no point of locating the last row of record on either sheet since they will always carry the full data(Both keeps appending as employee enters data.)

The vba I had written up was design for scenario that the Employee Tracker is like a daily, weekly, or monthly record. Once it's completed, the Save Data button copy and append the record in the Master Tracker workbook. Since then, the old record will be erased on the Employee Tracker file and start new.

Please let me know how exactly you would like both workbooks to work.

BTW, I have fixed the vba code. Also, I have set the file part of the Master-Tracker file to your server location. This way, the users of individual Employee-Tracker sheets don't need to have the Master-Tracker file opened for the buttons to work.

I have also added a Clear Data button to the Employee-Tracker sheet to help clear the data quickly and properly.
EMPLOYEE-TRACKER.xlsm
0
HEMIChallengerAuthor Commented:
HarryHYLee,

Sorry about the confusion. Yea I like to have the employee tracker to keep the old data they entered saved.  When they get new tickets they'll add the new entries in the next empty row and continue like this for a month, The reason i like to do this if they need to look up a ticket they entered they can look it up in there own employee tracker. That way they will never need to open the master tracker. I'm just worried some one will mess up the master tracker. I rather they mess up there own tracker if it gets corrupted some how.
0
Harry LeeCommented:
HEMIChallenger,

I have changed the code to suit your need.

Please test it out.

Make sure the Master-Tracker is in the server folder you have specified. The Command Button will open up the Master-Tracker workbook automatically, save the changes, and close automatically. Therefore it has to be in the right place.
EMPLOYEE-TRACKER.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HEMIChallengerAuthor Commented:
This works perfect I tested it at home and works just perfect. I'll test it tomorrow when im back at the office. Thank you very much.
0
HEMIChallengerAuthor Commented:
It works perfect thank you very much Sir.
0
HEMIChallengerAuthor Commented:
Works perfect thank you.
0
Harry LeeCommented:
You are welcome! I'm glad I can help.
0
HEMIChallengerAuthor Commented:
Hello,

Could the script update the file path name with a cell value? Example F1 would have a drop down list of the months. when you select one that would insert the correct month in your script so not to have to go in and edit the userform with the correct name?

set = range("f1").value

"C:\Users\hlee\Desktop\Excel Tests\Q_28263718\F1 CELL VALUE Master-Tracker.xlsm"

Or should i open a new ticket.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.