VBA Data Transfer - Search for cell value name in one workbook and update records from another

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hi, I am new to VBA and have been searching for codes to adapt to my needs for days and I keep hitting road blocks with my limited coding knowledge, so I'm finally surrendering my stubbornness and begging for help!
I have a workbook for individual training records that I need to update into a total "Personnel Master File". This master file is huge (165 columns across for different workplace requirements). I would like my macro to get the name in "E1" of the "Individual Training Records" page, look for the name in column "B" of the "Personnel Master File" and if found, copy and paste the training "Training Status" into the respective row cells for that individual. Note, the columns for the two workbooks will be out of alignment as there are many columns in the master file not associated to the training update workbook. I would really appreciate someone's expert advice on this.
Thanks you very much

Goal - Update this record to the Personnel Master File.
Individual Training RecordsName:(E1) Bugs Bunny
Employee Number:
12345​
Role:Digger
Individual Training Records
TrainingDurationRefresh****Training Status****EvidenceEO SignatureDate
Induction0.5Once Only (N/A)C
Safety0.5Once Only (N/A)C
Environmental Awareness0.3Once Only (N/A)C
Trade2Once Only (N/A)NYC
Quality Introduction0.3Once Only (N/A)
Security1.5Once Only (N/A)C

Personnel Master File...
Employee No.
Name (B1)Original TradeCurrent TradeSupervisorSupervisor EmailLocationMandatory TrainingInductionSecurityEnvironmentalQuality IntroductionTradeSafety
12346​
Daffy DuckFlight squadFlight squadElmer Fuddelmerfudd@warnerbros.com.auB20C
12347​
Porky PigDisposalDisposalElmer Fuddelmerfudd@warnerbros.com.auB20CC
12345​
Bugs BunnyDiggerDiggerElmer Fuddelmerfudd@warnerbros.com.auBurrow 1CNYCC
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
Hi,

What you want to achieve should be doable but would be more helpful if you could place copy of each of your worksheets with dummy data, using MrExcel Addin: XL2BB - Excel Range to BBCode

or better still, place copy of the workbooks on a file sharing site like dropbox & provide link to it.



Dave
 

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hi,

What you want to achieve should be doable but would be more helpful if you could place copy of each of your worksheets with dummy data, using MrExcel Addin: XL2BB - Excel Range to BBCode

or better still, place copy of the workbooks on a file sharing site like dropbox & provide link to it.



Dave
Thanks so much Dave,
I haven't used the XL2BB before - I hope the information is sufficient?
I would really appreciate any help I can get with this somewhat challenging project!

Individual Training Sheet.xlsm
F
134
Indiv Training


Personnel Master File.xlsx
G
1
Personnel Master File

Personnel Master File.xlsx
G
1
Personnel Master File
 

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Thanks so much Dave,
I haven't used the XL2BB before - I hope the information is sufficient?
I would really appreciate any help I can get with this somewhat challenging project!

Individual Training Sheet.xlsm
F
134
Indiv Training


Personnel Master File.xlsx
G
1
Personnel Master File

Personnel Master File.xlsx
G
1
Personnel Master File
Please ignore the last post, I had not selected the entire range, which I have since discovered is too much for this post. I will create a drop box link for the two files.
Thanks
 

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Please ignore the last post, I had not selected the entire range, which I have since discovered is too much for this post. I will create a drop box link for the two files.
Thanks

Please see Dropbox link below - Thanks

 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have found a moment to have a look at your file which is large & can’t help wondering with what you are trying to do would be better to consider using a database application like Access.

If though you want to stay with Excel then I would suggest that you first start with the master file & organise the data in to a flat file (single table) database.

A single database table will make it easier to read / write data and allows the easier creation of reports, pivot tables etc.

I have attached an update copy of your master file you can use as a basis but just be mindful, it is intended to work in the background as a database & should be kept as simple as possible.

Once you have settled this then your next step would be to structure that data entry workbook template to write the data to your database – not any easy task given the amount of data you are collecting.

At moment, I have major family event going on so cannot give your project too much attention but I will assist where I can but there are plenty of others here.

Hope helpful

Dave

 

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have found a moment to have a look at your file which is large & can’t help wondering with what you are trying to do would be better to consider using a database application like Access.

If though you want to stay with Excel then I would suggest that you first start with the master file & organise the data in to a flat file (single table) database.

A single database table will make it easier to read / write data and allows the easier creation of reports, pivot tables etc.

I have attached an update copy of your master file you can use as a basis but just be mindful, it is intended to work in the background as a database & should be kept as simple as possible.

Once you have settled this then your next step would be to structure that data entry workbook template to write the data to your database – not any easy task given the amount of data you are collecting.

At moment, I have major family event going on so cannot give your project too much attention but I will assist where I can but there are plenty of others here.

Hope helpful

Dave

Thank you so much Dave for taking the time to look at my project, appreciate your help.
I'll endeavour to simplify the master file for a more user friendly template.
Good luck with your family event
Emma
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,641
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top