Pasting tables, renaming sheets based on the cell values

Hi,

I am actually facing some difficulties with a certain problem.

I want to copy data from one file (attached) into a different one that is located here C:\Users\pllmkd\Desktop\Zdjęcia warsztaty\sample.xlsm

My problem is actually this:
- I want to copy contents that are formatted as tables and I want them to stay as tables after pasting in into another file
- there are hundreds of different files and they each are named in a certain way, the example I am sending is entitled "xyz.xlsx" but the sheets in the worbooks are always named this way: 2015, 2016, Arkusz3
- what I'd like to accomplish is after pasting contents of those tables into another file I want also to rename those new worksheets like this: in cell B1 there is value = Dawid xyz and in A2 is 2017( or 2016 and 2015 depending on the worksheet), and I woudl like to call those new sheets like this xyz 2017, xyz 2016 and xyz 2015?

Anyone could assist me with this problem?

Many thanks in advance.

Thanks for the answer.

Would it be enough to say that they are named starting from 'test1.xlsx' to 'test37.xlsx' and they are all placed in the folder path C:\Users\pllmkd\Desktop\Karty urlopowe
?

I am not sure if I understand you correctly. But I want to copy all of those files into one file but each worksheet from the copied files should be copied into a seperate worksheet in the file that is located in here C:\Users\pllmkd\Desktop\Zdjęcia warsztaty\sample.xlsm

So if there is the first file which I attached in my original post I want all those worksheets be copied to the destination file.

And for instance for the attached file there are three different worksheets: 2015, 2016 and Arkusz3, and I want them to be named like this: xyz 2015, xyz 2016 xyz 2017
* xyz is actually what is inside the value B2 (I want to extract all the characters that are placed after the first space in that cell)


I hope I made it clear but if you have any doubts please let me know, I will try to be more specific.

Wonderful. Works perfectly. Thank you very much for your help. Rep point already added