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