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

[EmmaFos]

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 Records			Name:	(E1) Bugs Bunny
			Employee Number:	12345​
			Role:	Digger
Individual Training Records
Training	Duration	Refresh	****Training Status****	Evidence	EO Signature	Date
Induction	0.5	Once Only (N/A)	C
Safety	0.5	Once Only (N/A)	C
Environmental Awareness	0.3	Once Only (N/A)	C
Trade	2	Once Only (N/A)	NYC
Quality Introduction	0.3	Once Only (N/A)
Security	1.5	Once Only (N/A)	C

Personnel Master File...

Employee No.	Name (B1)	Original Trade	Current Trade	Supervisor	Supervisor Email	Location	Mandatory Training	Induction	Security	Environmental	Quality Introduction	Trade	Safety
12346​	Daffy Duck	Flight squad	Flight squad	Elmer Fudd	elmerfudd@warnerbros.com.au	B20					C
12347​	Porky Pig	Disposal	Disposal	Elmer Fudd	elmerfudd@warnerbros.com.au	B20	C			C
12345​	Bugs Bunny	Digger	Digger	Elmer Fudd	elmerfudd@warnerbros.com.au	Burrow 1		C				NYC	C

 

[dmt32]

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]

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]

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]

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

Dropbox

www.dropbox.com

 

[dmt32]

Please see Dropbox link below - Thanks

OK I have it - it's quite a complex & not sure have an immediate answer but will have a think

Dave

 

[dmt32]

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

Dropbox

www.dropbox.com

 

[EmmaFos]

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

Dropbox

www.dropbox.com

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