CODEWITHSUNDEEP
excelms-accesschartsvba
FreeMan
FreeMan

Reputation: 5687

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

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.

Upvotes: 1

Views: 64

Answers (1)

FreeMan
FreeMan

Reputation: 5687

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.

Upvotes: 1

Related Questions