Copy Sheets and Rename

[lordofthefart]

Hi,

I have the following code that is supposed to take input from a control sheet called 'input' which has file path and name that is supposed to be in cells A5 (the name of the sheet to be renamed), C5(the file path) and D5(the file name) and E5(C5&D5). I want the macro to be able to scroll through rows 5-13 and open up the file in the specified file path, copy a sheet called "attestation" and paste it back in the original controlfile. I would then like the tab that was just copied to be pasted at the last sheet in the controlfile and renamed based on the cell value of the input tab cell A5.

I would then like to put a formula in the cells of the new sheet and lastly close the workbook that the sheet was copied from.

I would then like this macro to scroll through the rows 5-13 and end up with 9 new tabs in my controlfile.

The code pasted below is what I have so far; but I'm not sure why it only copies 5 of the sheets as opposed to 9 and the rename doesn't work.

Please Login or Register  to view this content.

thanks!

[JBeaucaire]

Your use of ON ERROR RESUME NEXT is why you don't know when/where it fails. Only use that when you're done designing your code and you know it's bulletproof. You're basically telling the macro to barrel on ahead when something doesn't work. So take that out and you'll see when/where it fails, can DEBUG and hover your mouse over variables of the failed line of code to see the current values.

I would guess that the filenames are not 100% accurate and 4 are not being found. Could be a hidden space in the real filenames or in the cells with the filenames.