VBA Copy and Create File Using Sheet Name

Hi, I wonder whether someone may be able to help me please.

I'm trying to put together a script which peforms the following:

• Takes all but four sheets in a workbook,

• Copies each sheet, and then

• Pastes this into a new file, using the sheet name as the filename, and then

• Automatically save each file to a specific folder

The code below is what I've put together, but unfortunately I'm receiving a 'Run time error '91' with debug highlighting this line as the cause:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I just wondered whether someone could possibly look at this please and let me know where I'm going wrong, and provide some guidance on how I may be able to automatically save the file to a specific location, which is "Z:\Extracted Reports"

Many thanks and kind regards

Hi @Kaper, thank you very much for taking the time to reply to my post, and for the comprehensive reply, I've found it to be very interesting.

You can probably tell that I'm relatively new to VB and so I will, and obviously have made some basic errors. So in respect of setting an array, this was the only way in which I knew to select all the relevant sheets, but I'm more than happy to be told of a better method.

In relation to what I'm trying to achieve:

• Excluding the four sheets highlighted,

• Copy each sheet independently, then

• Paste into a new workbook, then

• Save each file to the location "Z:\Extracted Files" with the sheet name being used as the filename

I hope this helps, and once again, many thanks for your help.

Kind Regards

Hi @Kaper, I hope you are well.

I feel I need to offer you my sincere apologies, because when I replied to your post yesterday I clearly did so without reading your post properly.

The solution you kindly provided (copy below) works perfectly. I just made some minor tweaks by adding a 'Save Directory' line of code, and it's exactly what I was after.

Please Login or Register  to view this content.

Once again my apologies and thanks

All the best and kind regards