VBA To Pull Data from one Worksheet and Place into another Based on Common Data Identifier

[NKlawender]

Hello all,
I am learning a lot in my first few days of trying to write VBA code for a work application.
The issue that I don't have any idea yet how to solve is this. I have two worksheets, one is survey data and one is basically a reference worksheet. They both have a column called Offering that contains a number. I work in training and this is how our classes that are created are identified. I want to look at the survey data Offering cell, then find that same Offering number in the reference worksheet and copy over several other pieces of data. I would want this to look at each row and then stop when it gets to the end. I apologize that I don't have any sample code yet, I just started trying to learn this stuff this week.
Thank you,
Norm

 

[Joe4]

Couldn't this be easily accomplished with the use of VLOOKUP formulas?
If you have a dynamic number of rows, you could use VBA to populate the correct number of rows of these VLOOKUP formulas.

If you need further assitance, it would of great help to us if you could post samples of your data from each sheet, so we can see what you are working with, and see where you would like the information returned. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[NKlawender]

Thank you. I will investigate VLOOKUP formulas and try to put something together to see if it works. If not, I will reply back. I think I just needed a place to start.

 

[Joe4]

Thank you. I will investigate VLOOKUP formulas and try to put something together to see if it works. If not, I will reply back. I think I just needed a place to start.

Sounds good.
Also note that there is an "XLOOKUP" formula, which can be used if the value you are matching on is not the left-most column in your data range.

 

[NKlawender]

I am trying to use the XLOOKUP formula. I can get it for one cell by just writing a formula to that cell. So for example, in cell G2, I have:

=XLOOKUP(H2,[TechSurveyBIData.xls]OfferingToInstructorAndATM!$A:$A,[TechSurveyBIData.xls]OfferingToInstructorAndATM!$E:$E)

which says to take value in cell H2 and find its match in column A of the other reference workbook, TechSurveyBIData.xls, then grab the corresponding data item from that row in column E. I am having a hard time figuring out how to write it in VBA so that it will fill every row in column G with the correct information.
Sorry if this is such a basic question.
Thanks,
Norm

 

[Joe4]

Let Excel do all the hard work for you!

Turn on your Macro Recorder, and record yourself entering the formula in cell G2.
Then stop the Macro Recorder, and copy the part of the code with the formula, and plug it into your code.

If you have any problems getting that to work, paste the code in here, along with a description of how we can determine which and how many rows need the formula.

 

[NKlawender]

Here is what I have. The code is hideous where I am renaming the columns, but it works. The part that I am trying to get now is the part after 'Insert Offering Facility from BI Data. Those next 2 lines of code are what I copied and pasted from the Macro Recorder. I need it to repeat for each row. There will be a different number of rows each time I run this report (monthly).

Sub EnglishILT()
Dim wbNew As Workbook
Workbooks.Open "C:\Users\T8811NK\Desktop\Perception Survey Files\2022 Data\03\TechSurveyBIData.xls"
Workbooks.Open "C:\Users\T8811NK\Desktop\Perception Survey Files\2022 Data\03\Results_Items_4330385983076042.csv"
Worksheets("Results_Items_4330385983076042").Copy
Set wbNew = ActiveWorkbook
'Delete columns
Sheets("Results_Items_4330385983076042").Range("A:A,D:H,J:L,R:AD,AG:BU,BW:CE,CG:CO,CQ:CY,DA:DI,DK:DS,DU:EC,EE:EM,EO:EW,EY:FG,FI:FQ,FS:GA,GC:GK,GM:GU,GW:HB").EntireColumn.Delete
'Move Dealer code column next to technician name column
Columns("F").Cut
Columns("E").Insert Shift:=xlToRight
'Insert new columns
Range("G1").EntireColumn.Insert
Range("I1").EntireColumn.Insert
Range("K:P").EntireColumn.Insert
Range("V1").EntireColumn.Insert
Range("AD:AG").EntireColumn.Insert
'Insert new column names
Range("A1").Value = "Assessment Name"
Range("B1").Value = "Assessment Type"
Range("C1").Value = "Participant ID"
Range("D1").Value = "Participant Name"
Range("E1").Value = "Dealer Code"
Range("F1").Value = "Course Code"
Range("G1").Value = "Course Name"
Range("H1").Value = "Offering ID"
Range("I1").Value = "Offering Facility"
Range("J1").Value = "Instructor ID"
Range("K1").Value = "Instructor 1 Name"
Range("L1").Value = "Instructor 1 ATTM"
Range("M1").Value = "Instructor 2 Name"
Range("N1").Value = "Instructor 2 ATTM"
Range("O1").Value = "Instructor 3 Name"
Range("P1").Value = "Instructor 3 ATTM"
Range("Q1").Value = "Date/Time Started"
Range("R1").Value = "Date/Time Finished"
Range("S1").Value = "The course content was easy to understand"
Range("T1").Value = "The examples and/or activities helped me understand the course content."
Range("U1").Value = "The instructor delivered the course in a well-organized way."
Range("V1").Value = "The course was delivered in a well-organized way."
Range("W1").Value = "The duration of the training was appropriate based on the content covered."
Range("X1").Value = "The course content was relevant to my job."
Range("Y1").Value = "Would you recommend this course to a colleague?"
Range("Z1").Value = "Are you satisfied with the training?"
Range("AA1").Value = "What did you appreciate most? What would you change?"
Range("AB1").Value = "The instructor(s) were knowledgeable about the subject."
Range("AC1").Value = "The instructor(s) actively engaged me as a participant."
Range("AD1").Value = "The virtual environment was an effective way to present this class."
Range("AE1").Value = "I felt engaged with other technicians in the course"
Range("AF1").Value = "My dealer provided me with the necessary equipment and support to be successful in this course."
Range("AG1").Value = "What did you like most/least about learning in the virtual environment?"
Range("AH1").Value = "I would take another course from this instructor"
Range("AI1").Value = "I will use the course materials (manual, presentation handouts, etc.) on the job"
Range("AJ1").Value = "I found the instructional aides (e.g. tools, components, vehicles, etc.) were in a usable condition to support my learning"
Range("AK1").Value = "What recommendations do you have for course improvement?"
'Insert "ILT" into column B
Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value = "ILT"
'Insert Offering Facility from BI Data
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[-1],[TechSurveyBIData.xls]OfferingToInstructorAndATM!C1,[TechSurveyBIData.xls]OfferingToInstructorAndATM!C8)"
'Save the new clean file 51 is .xlsx, 52 is .xlsm
wbNew.SaveAs "C:\Users\T8811NK\Desktop\Perception Survey Files\2022 Data\03\English ILT Clean.xlsx", 51 'Adjust filepath to w drive as necessary
'Close the new workbook
wbNew.Close True
Workbooks("TechSurveyBIData.xls").Close SaveChanges:=False
Workbooks("Results_Items_4330385983076042.csv").Close SaveChanges:=False
End Sub

 

[Joe4]

You can use the same logic as you did for column B above, i.e.

Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value = "ILT"
'Insert Offering Facility from BI Data
Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = _
    "=XLOOKUP(RC[-1],[TechSurveyBIData.xls]OfferingToInstructorAndATM!C1,[TechSurveyBIData.xls]OfferingToInstructorAndATM!C8)"

 

[NKlawender]

Great, I was hoping thaT would work.
I will let you know how it works out.
Norm