Unable to get the Copy property of the Worksheet class

Question

I'm trying to copy a worksheet from workbook into another using Worksheets.Copy function. However, this seem to be working in only some of the files and for others I get "Unable to get the Copy property of the Worksheet class" error message. I've tried to copy/move worksheets manually from other workbooks. Also, tried unprotecting/protecting workbook and worksheets but none seem to be working. I'm using Microsoft Office 2010. Is there an Excel feature that enables/disables copying worksheets form other workbooks?

Answer 1

In case someone stumbles upon this in the future, my issue was that the worksheet I was trying to copy was set to 'xlSheetVeryHidden'. To overcome this issue, I temporarily made the sheet visible and made it hidden again straight after the Copy call.

templateWorksheet.Visible = xlSheetVisible
Call templateWorksheet.Copy
templateWorksheet.Visible = xlSheetVeryHidden

Answer 2

In my case the issue was that I had not been working with the worksheet but the workbook. Make sure you are using the worksheet and not the book.

Answer 3

In my case, the Excel file was not enabled for editing. Once I did that, it worked.