Copy from each workbook in a folder to specific sheets in another workbook based on their name

[rjh3m8]

Hi,
I have data for a group of people. Each person has their own folder of data, and each item of data has it's own workbook. I have also created master workbooks for each person, with each item's data on a different sheet in the master workbook.

I'd like to be able to copy the data from each worksheet in the person's folder to the corresponding sheet in the master workbook. I have code already that will open each file in a folder and perform an action, but I am struggling to write code for identifying which item it's copying from and which sheet it should copy to. For example, the filename may be "Person01_Item12" and it should be copied to workbook "MasterPerson01" sheet "Day1_Item12." Mostly the item numbers correspond to the sheet numbers, but not all of them.

Is there a way to have it look at the end of the file name (where the item number is) and then reference a list that indicates which sheet it should copy to in the master workbook? I am struggling to figure out how to get it to know that when it says "Item 12" it should go to sheet "Day1_Item 12" and when it says "Item 32" it should go to "Day 2_Item 17"

Any ideas? Let me know if more detail is needed. Thanks in advance!!

 

[Micron]

Is there a way to have it look at the end of the file name (where the item number is)

If that's all you need to solve this, should not be too hard. If the file names all follow this format: Person01_Item12 then (Fname is a variable here for the filename):

Mid(Fname,Instr(Fname,"_")+1)

You could replace Fname with a range address if need be.

 

[rjh3m8]

Great, that will let me code for finding the file name. Is there then a way to have a list that it can reference to know which sheet to put the copied data into? For instance, if the filename is "Person01_Item12" the sheet in the master workbook is named "Day1_Item12," or if the filename is Person01_Item28 the sheet in the master workbook is named "Day2_Item3." So the sheet name and filename are not the same; however, the particular item number will always go to a specific sheet.

Is there a way to tell it where to paste the data based on the filename?