See how to use Power Query to easily import multiple sheets of data from multiple Excel Workbooks and easily have Power Query Add a new column that is populated with the Sales Rep Name from the Sheet Tab:
(00:10) Problem Setup
(01:32) Import From Folder
(01:55) Remove Other Columns
(02:05) Add Custom Column with =Excel.Workbook([Content]) to get Excel Workbook Content
(02:38) Remove Column
(03:02) Filter by “.Kind” to Filter out Tables and Defined Names
(03:58) Use Filter Feature to Filter out Sheet Tab Names that do not contain a proper Sales Rep Name.
(05:20) Add Custom Column to Promote Headers with =Table.PromoteHeaders(“*.Data”)
(06:16) Expand Tables to create on large table.
(06:54) Add Data Types.
(07:33) Save and Load to Table in Excel Worksheet.
(07:57) Set Query Property to Update When File Is Opened.
(08:27) Add New Sheets to Source Files and see that Power Query Updates Automatically.