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

sublimus

New Member
Joined
Jun 17, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited by a moderator:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,997
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Post the code as you have it now (in code tags) and not as an image.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try running this"

VBA Code:
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

New Member
Joined
Jun 17, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Thank you for trying with me. But still getting an error at line .value = .value
1592596095277.png
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You did not mention that you were trying to run this on a pivot table. I'm outta here!
 

sublimus

New Member
Joined
Jun 17, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,889
Messages
5,627,481
Members
416,250
Latest member
darius_rebelo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top