Worksheet.copy() fails for _only_ one worksheet in a workbook

Question

I am running VBA code from Access 2010 that opens an Excel 2010 workbook ("Master"), then copies specified sheets from "Master" to a "Client" workbook. This has worked fine until I got to one particular worksheet, where I get:

Run-time error '1004': Copy method of Worksheet class failed

The line of code is:

XLMaster.Sheets(SlideRS.Fields(2).Value).Copy _
         After:=XLClinic.Sheets(XLClinic.Sheets.Count)

In this particular instance, this is the 2nd sheet being copied, so the code worked the first time through. This is part of a long debugging process, and this line of code has worked great for more than a dozen other worksheets from the same "Master" workbook.

Oddly, the copy fails when I try to do it manually, as well. I've opened "Master", right clicked the particular worksheet that's failing, and tried to copy it, and it just simply fails to do so. I can manually copy other worksheets within the workbook just fine.

The only difference I can identify is that this particular worksheet has 2 pivot charts on it. Other worksheets that copy OK have just data, charts (of varying types), and even pivot charts and regular charts. This is the only one that has only pivot charts and not 'regular' charts. Not sure if that's the cause, but that's the only thing I can identify that's possibly different.

For greater context, the line of code comes from this loop:

  While Not SlideRS.EOF
    If SlideRS.Fields(1) <> SlideRS.Fields(2) Then                        
     'the worksheet depends on something else, copy it first
     'if the depended upon slide is not in the list of UsedSlides, then add it
      If InStr(1, UsedSlides, SlideRS.Fields(2)) = 0 Then                 
        XLMaster.Sheets(SlideRS.Fields(2).Value).Copy _
                 After:=XLClinic.Sheets(XLClinic.Sheets.Count)
        Set NewSheet = XLClinic.Sheets(XLClinic.Sheets.Count)
        UsedSlides = UsedSlides & "," & NewSheet.Name
        UpdateCharts XLMaster.Sheets(SlideRS.Fields(2).Value), NewSheet
        ProcessDataSheet NewSheet, NewXLName
        Set NewSheet = Nothing
      End If
    End If
    SlideRS.MoveNext
  Wend

Again, this code works just fine, it's just this one particular sheet that doesn't want to be copied, either by code or by hand.

Here is what happens when I attempt to copy it by hand in the "Master" spreadsheet.

In a freshly opened copy of my "Master" workbook, I opened the code window and executed Application.DisplayAlerts = True in the immediate window (just to be sure), then,

the worksheets before the copy: enter image description here
^{VisitsByDemo is the sheet being copied}

The copy setup: enter image description here

And after the copy: enter image description here

There is no error message generated by Excel.

@BranislavKollár No, there is not. When I manually copy a worksheet in the open workbook, it gets named <sheetname> (2). I believe this was happening before I added the code to prevent duplicates in my copy routine, as well. — FreeMan
Q1 : When doing manual copy, is there any error, regular excel error? Q2 : Can this odd behaviour be caused by protection (password)? Q3 : Does this happen when copying the sheet into new (clean) workbook? Q4 : Can you make a copy of the sheet inside Master workbook? Q5: Can you confirm your thoughts about the pivot charts, by deleting them (in some test workbook of course ;) )? — kolcinx
Q1 see update in OP. Q2 I'd double checked that previously, no, there is no protection on the WB or the WS. Q3 Hmmm... when doing it manually, I CAN copy it to a brand new workbook. Q4 No, see updates in OP. Q5 Based on Q3, that doesn't seem to be the issue, but I may have to look into it some more — FreeMan
Q3 update: I moved the worksheet to a new workbook (unintentionally). That worked. When I tried to copy it to a new workbook, that still fails. — FreeMan

FreeMan FreeMan · Accepted Answer · 2015-04-02T14:51:36

It turns out the the PivotCharts were broken.

When I attempted to edit the data source for the PivotCharts, this is what Excel showed me: enter image description here

When I recreated the PivotCharts on that page, then edit the data source, this is what I see:

enter image description here

Not sure what happened, but since I created new versions and deleted the originals, Worksheet.Copy() works just fine.

Worksheet.copy() fails for _only_ one worksheet in a workbook

1 Answers