Complete beginner to VBA,

[Linsie]

Hi,
I am venturing into VBA, I have seen some videos, but its not clicking with me atm.

I want 3 functions to autorun on each sheet of the 4 sheets. The first 3 sheets all have the same A-C columns. I would like B&C to auto populate when then engineers name is entered in A. The data is can lookup is in the Data Sheet. ( I would usually use for B =IFERROR(LOOKUP([@[Engineer Name *]],Table4[Full],Table4[Manager]),"") and for C =IFERROR(LOOKUP([@[Engineer Name *]],Table4[Full],Table4[Team]),"") for that)

Example Sheet (1) Trainings Taken <2 Yrs
Name of Joe Bloggs 1 is entered into the Engineer Name field (A2), that would then automatically trigger B2 to find the Managers name, and C2 to return the team name from the table found on the Data Sheet and to also auto size each column.

I know for auto size I can use the below, but I don't know how to put it all together.

For Each ws In Worksheets
ws.Columns.AutoFit
Next ws

Certification Data v2.xlsm
	A	B	C	D	E	F	G
1	Engineer Name *	Manager Name (Autofill)	Team (Autofill)	Date (mm-yyyy) *	Course Name *	Level *	Vendor *
2
3
4
5
6
(1) Trainings Taken <2 Yrs

Certification Data v2.xlsm
	A	B	C	D
1	Full	Team	Manager	Country
2	Joe Bloggs 1	ODCM	ODCM MGR	NL
3	Joe Bloggs 2	OSES	OSES MGR	UK
4	Joe Bloggs 3	OSS	OSES MGR	NL
5	Joe Bloggs 4	Group	Director	UK
6	Joe Bloggs 5	Band 6	B6 MGR	IE
7	Joe Bloggs 6	ODCM	ODCM MGR	UK
8	Joe Bloggs 7	PSD	OSS MGR	UK
9	Joe Bloggs 8	OSES	OSES MGR	NL
10	Joe Bloggs 9	OSS	OSS MGR	UK
11	Joe Bloggs 10	OSES	OSES MGR	DE
12	Joe Bloggs 11	OSES	OSES MGR	UK
13	Joe Bloggs 12	GNO-A	GNO-A MGR	UK
14	Joe Bloggs 13	OSS	OSS MGR	NL
15	Joe Bloggs 14	ODCM	ODCM MGR	AU
16	Joe Bloggs 15	OSS	OSS MGR	UK
Data

 

[Burrgogi]

For your autofit part, try this:

Sub AutoFitCol_All_Sheets()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.UsedRange.Columns.AutoFit
    Next ws
End Sub

Make you copy & paste that code into the "ThisWorkbook" module. Say for your example your excel file is named "Book6.xlsx".

Open up the VBA editor (Alt + F11). Locate Book6 and double-click on the "ThisWorkbook" module as shown in the screenshot. Then copy & paste the code I gave you.

 

[Burrgogi]

One additional comment to make. What you're asking for are 2 different types of events. The first "event" is that you want Excel automatically populate columns B & C, whenever a user types in the engineer's name into column A. That's one.

The second event you're asking for (regarding autofit) is just a 1-time deal. You do not need to run it each time that the 1st event is triggered. You just need to run that once and you're done. Therefore you should be looking for 2 separate macros independent of each other. Make sense?

 

[jkpieterse]

Why use a macro to get those names, you already have a feasible formula?

 

[Linsie]

For your autofit part, try this:

Sub AutoFitCol_All_Sheets()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.UsedRange.Columns.AutoFit
    Next ws
End Sub

Make you copy & paste that code into the "ThisWorkbook" module. Say for your example your excel file is named "Book6.xlsx".

Open up the VBA editor (Alt + F11). Locate Book6 and double-click on the "ThisWorkbook" module as shown in the screenshot. Then copy & paste the code I gave you.

View attachment 111352

Thanks for this and the explanation below.