Copy method of worksheet class failed" with pivot table

[programsam]

I have a pivot table created in Power Pivot, it's on a sheet ("Sups") that when using VBA to copy and paste will work sometimes and in other instances, it will not, triggering the "Copy method of worksheet class failed" error.

The line that triggers the error is below. Any thoughts?

Sub sendEmail()

Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim OutMail As Outlook.MailItem
Set OutMail = outlookApp.CreateItem(olMailItem)
Dim toList As String, ccList As String, filepath As String

Dim year As String
Dim today As Date, yesterday As Date
Dim dailyWb As Workbook
Dim openWb As Workbook
Set dailyWb = ThisWorkbook
Dim range_data As Range, range_data2 As Range, range_data3 As Range, range_data4 As Range, range_data5 As Range
Dim path As String, stats As String

    year = dailyWb.Sheets("Values").Range("K5").Value
    today = dailyWb.Sheets("Values").Range("K1").Value
    yesterday = dailyWb.Sheets("Values").Range("K2").Value
    filepath = dailyWb.Sheets("Values").Range("K6").Value
    toList = dailyWb.Sheets("Values").Range("K13").Value
    ccList = dailyWb.Sheets("Values").Range("K14").Value
    stats = dailyWb.Sheets("Values").Range("C22").Value
    Call refresher
Set newWb = Workbooks.Add

'Copying a worksheet from ThisWorkbook into newly created workbook in the above statement
    dailyWb.Worksheets("Reps").Visible = True
    dailyWb.Sheets("Reps").Copy Before:=newWb.Sheets(1)
    newWb.Sheets(1).UsedRange.Value = ActiveSheet.UsedRange.Value
    newWb.Sheets(1).Columns("A:K").Delete
    newWb.Sheets(1).Range("C3").Select
    ActiveWindow.FreezePanes = True
    dailyWb.Worksheets("Booked Orders").Visible = True
    dailyWb.Sheets("Booked Orders").Copy After:=newWb.Sheets("Reps")
    'newWb.Sheets("New Orders").Columns("O").Delete
    dailyWb.Worksheets("New Orders").Visible = True
    dailyWb.Sheets("New Orders").Copy After:=newWb.Sheets("Booked Orders")
    newWb.Sheets("New Orders").Columns("O").Delete
    'newWB.Sheets("New Orders").UsedRange.Value = ActiveSheet.UsedRange.Value
    dailyWb.Worksheets("Shipped Sales").Visible = True
    dailyWb.Sheets("Shipped Sales").Copy Before:=newWb.Sheets("New Orders")
    'newWB.Sheets("New Orders").UsedRange.Value = ActiveSheet.UsedRange.Value
    dailyWb.Worksheets("Rep Bonus").Visible = True
    dailyWb.Sheets("Rep Bonus").Copy After:=newWb.Sheets("New Orders")
    'newWb.Sheets("Rep Bonus").UsedRange.Value = ActiveSheet.UsedRange.Value
    dailyWb.Worksheets("Dispositions MTD").Visible = True
    dailyWb.Sheets("Dispositions MTD").Copy Before:=newWb.Sheets("Rep Bonus")
    dailyWb.Worksheets("Interaction").Visible = True
    dailyWb.Sheets("Interaction").Copy Before:=newWb.Sheets("Reps Bonus")
    dailyWb.Worksheets("Sups").Visible = True
[B]    dailyWb.Sheets("Sups").Copy Before:=newWb.Sheets("Reps Bonus")[/B]
    newWb.Sheets("Reps").Activate

End Sub

 

[RoryA]

Your code copies Rep Bonus but subsequent lines (including your problem one) refer to Reps Bonus - is that intentional?

 

[programsam]

Your code copies Rep Bonus but subsequent lines (including your problem one) refer to Reps Bonus - is that intentional?

no, they all match up... I did a find/replace and then manually edited a few lines. The script checks out in Excel and it works about 40% of the time but for some reason, it doesn't with others.

I'm not sure if it has something to do with the Pivot being a Power Pivot table or not.

 

[RoryA]

I'm not sure if it has something to do with the Pivot being a Power Pivot table or not.

If it were, then the code would never work.

 

[programsam]

If it were, then the code would never work.

I figured out the cause, now to find the solution.

The Worksheet that triggers the error is a pivot table created using Power Pivot. Whenever Excel tries to copy that sheet, at the bottom you can read "Loading Data Model". What I've just uncovered is that there is an issue with loading the data model because when you look at the "Queries and Connections" pane, you will see where Power Query is somehow renaming the connections by adding a trailing number wrapped in parentheses. So a connectino named "New Orders" is renamed "New Orders (2)" or "New Orders (3)".

Weird issue.