Run-time error '1004': You can't paste this here because the copy area and paste area aren't the same size

[sublimus]

Hello Everyone:
I've been using the below macro for years, and they just recently upgraded my machine to Excel 2019. Now I get the above run time error. Can anyone tell me what I need to change so that it will function again?

Thank you.

Sub Mailing()

Dim FileExtStr As String

Dim FileFormatNum As Long

Dim Sourcewb As Workbook

Dim Destwb As Workbook

Dim TempFilePath As String

Dim TempFileName As String

Dim OutApp As Object

Dim OutMail As Object

Dim sh As Worksheet

Dim TheActiveWindow As Window

Dim TempWindow As Window

With Application

.ScreenUpdating = False

.EnableEvents = False

End With

Set Sourcewb = ActiveWorkbook

With Sourcewb

Set TheActiveWindow = ActiveWindow

Set TempWindow = .NewWindow

.Sheets(Array("AS", "AS Sum")).Copy

End With

TempWindow.Close

Set Destwb = ActiveWorkbook

With Destwb

If Val(Application.Version) < 12 Then

FileExtStr = ".xls": FileFormatNum = -4143

Else

Select Case Sourcewb.FileFormat

Case 51: FileExtStr = ".xlsx": FileFormatNum = 51

Case 52:

If .HasVBProject Then

FileExtStr = ".xlsm": FileFormatNum = 52

Else

FileExtStr = ".xlsx": FileFormatNum = 51

End If

Case 56: FileExtStr = ".xls": FileFormatNum = 56

Case Else: FileExtStr = ".xlsb": FileFormatNum = 50

End Select

End If

End With

For Each sh In Destwb.Worksheets

sh.Select

With sh.UsedRange

.Cells.Copy

>>.Cells.PasteSpecial xlPasteValuesAndNumberFormats

.Cells(1).Select

End With

Application.CutCopyMode = False

Destwb.Worksheets(1).Select

Next sh
TempFilePath = Environ$("temp") & "\"

TempFileName = "Audit Trail Report"

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

With Destwb

.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next

With OutMail

.to = "[EMAIL]Harry.Potter@hogwarts.edu[/EMAIL]"

.CC = " "

.BCC = " "

.Subject = "FY20 YTD Expense Report"

.Body = " Please review your FY20 YTD Expense Report. Thank you"

.Attachments.Add Destwb.FullName

.Attachments.Add ""

.Send 'or use .Display

End With

On Error GoTo 0

.Close savechanges:=False

End With

Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing

Set OutApp = Nothing

With Application

.ScreenUpdating = True

.EnableEvents = True

End With

End Sub

 

[MARK858]

Post the code as you have it now (in code tags) and not as an image.

 

[JLGWhiz]

Try running this"

Sub Mailing()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
Set Sourcewb = ActiveWorkbook
    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("AS", "AS Sum")).Copy
    End With
TempWindow.Close
Set Destwb = ActiveWorkbook
    With Destwb
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If
    End With
    For Each sh In Destwb.Worksheets
        With sh.UsedRange
            .Value = .Value
        End With
        Destwb.Worksheets(1).Select
    Next sh
TempFilePath = Environ$("temp") & "\"
TempFileName = "Audit Trail Report"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .to = "[EMAIL]Harry.Potter@hogwarts.edu[/EMAIL]"
            .CC = " "
            .BCC = " "
            .Subject = "FY20 YTD Expense Report"
            .Body = " Please review your FY20 YTD Expense Report. Thank you"
            .Attachments.Add Destwb.FullName
            .Attachments.Add ""
            .Send 'or use .Display
        End With
        On Error GoTo 0
        Err.Clear
        .Close savechanges:=False
    End With
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[sublimus]

Thank you for trying with me. But still getting an error at line .value = .value

 

[JLGWhiz]

You did not mention that you were trying to run this on a pivot table. I'm outta here!

 

[sublimus]

You did not mention that you were trying to run this on a pivot table. I'm outta here!

I'm sorry. As I said, I always used the code and it never mattered before. The only thing that changed was the version of excel. Thank you for your efforts. Much appreciated.