Debug error help: Method 'Copy' of Object '_Worksheet' failed

crosslink here: http://www.mrexcel.com/forum/excel-q...et-failed.html

Hi all,

I'm having an odd issue with my below macro.

The macro copies a tab from a current file and then does some manipulation then save's it to a specified folder.
(note: some non relevant code has been omitted to keep it short)

The code works great if i run it over and over but once i have opened the file which has been saved and then close it and try rerun the macro i get the debug error on red portion of the code "Method 'Copy' of Object '_Worksheet' failed"

Anyway i can prevent this from happening?

Please Login or Register  to view this content.

Hello,

I havent tried to run the code but is it a s simple as moving the "offending statement" down one line until it is after the Set wb1 statement. It looks like you dont have focus on the "Sheet1" so possibly the order is wrong or you need to actually open the workbook that you are trying to copy from??

Just a few random, rapid thoughts

put your macro in a file ran it closed it opened it re-ran it all works fine for me. is sheet1 having its name changed at some point? try changing it to

Please Login or Register  to view this content.

my actual sheet name is "Project Master" not "Sheet1" as per the code, does that make a difference?

Please Login or Register  to view this content.

the sheet it's copying is Sheet number 2. I've changed the code to below to test but having the same error this way

Please Login or Register  to view this content.

side note your spath ends in a \ and your save code ads a \ so your save location is currently Z:\Template\\Test.xlsx which fails.

if the macro is referencing Sheets("Project Master") and the sheet is called Project Master it should work.
what do you get if you put in

Please Login or Register  to view this content.

well spotted on the savepath, i forgot to remove the "\" after template in the above code. the actual filepath is much longer and doesn't include the erroneous "\".

If i run your above code message box says "Project Master" without inverted commas

I've noticed when i get the error my master file is open but there is also a new book open which is an exact copy of my whole master file including all tabs/macro buttons etc. not just the tab i'm trying to copy which i'd expect from the code.

try adding in

Please Login or Register  to view this content.

at the start and using

Please Login or Register  to view this content.

is the master file an old .xls file?

no not if it is .xlsm.
I can't recreat this to test it.
I run macro it copies sheet 2 and saves the copy as test.xlsx and closes it.
I open test.xlsx then close it
I run macro it copies sheet 2......etc (here is where you can't copy sheet 2 but you can get the name of sheet 2).

I can't think of any reason why it would not be able to copy a sheet it can see is there (and we know it can see it as you put in sheet(2).name in just befor copy and it was fine).