Hello,

This is my first post. I have looked for a few days now and have found several other solutions but nothing that accomplishes what I am trying to do. I am slightly familiar with VBA but not good enough to troubleshoot something quite like this.

I have a VBA script (below) that looks at all of the .xls files in a folder, which the path is entered in cell "E1", and copies a tab called "Germany" from each book into a master file. Instead of having dozens of tabs all called "Germany (1), Germany (2)....etc." I want the tabs to be renamed using the file name of the source from which they came. All source files are named the same thing with the exception of the first 4 charachters (e.g. ####_Q1 Results.xls). I would like each tab to be renamed using the first 4 characters of that file name. I have tried a variety of things but end up with the script trying to pull the first four characters of the master book leaving me with an error that says sheets cannot be named the same.


Sub GetSheets()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Path = Range("E1")
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
ActiveWorkbook.Sheets("Germany").Copy After:=ThisWorkbook.Sheets(1)

Workbooks(Filename).Close
Filename = Dir()
Loop
Application.DisplayAlerts = False
Application.ScreenUpdating = False
End Sub
Please help!

Thank you