Macro to copy data from multiple files into specific tabs in consolidated file

[Pyes]

Hi Everyone,

There's a piece of work I have to do on a monthly basis which can get quite repetitive. I have to copy the data from individual files and paste them in to different tabs in a master template. For example, we I have files called "Dept 0, Dept 1, Dept 2" etc and then the data from these files then have to go into the tab named "0, 1, 2" etc.

Is there a way that a macro can go through and copy the data from file Dept 0 and paste it into 0 and then go through each file in the specified folder and paste it into the tab? No formatting is needed on the data, it just needs to paste it into cell A1 in each tab.

I have been able to get a macro to copy data from multiple files all saved in one specific folder into one tab on the consolidated file, but I'm struggling to get the data to paste into the different tabs. All of the files will be saved in the same file so just selecting the folder to import will work well.

Any help is much appreciated.

Thanks,
Tom

 

[Pyes]

It's got a couple of steps further, but has tripped up at the below point:

It had opened up the first file "Dept 0" in the folder.

 

[mumps]

What error message do you get when you click "Debug"?

 

[Marc L]

Hi, as there is no check if only the 'tabname' worksheet exists at least …​

 

[Pyes]

It's still tripping over at the same point: