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

 

[JLGWhiz]

I think I would just delete this

With sh.UsedRange
.Cells.Copy
>>.Cells.PasteSpecial xlPasteValuesAndNumberFormats
.Cells(1).Select
End With

and replace it with this

With sh
    .UsedRange.Copy
    .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End With

 

[sublimus]

Now, I get a different error: "This selection isn't valid. Make sure the copy and paste areas don't overlap unless they are the same size and shape.

 

[MARK858]

What do you get with the below?

With sh.UsedRange
     .Copy
     .PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
     .Cells(1).Select
End With

 

[sublimus]

It keeps getting hung up on the same line. This time it says PasteSpecial method of Range class failed.

 

[MARK858]

Do you have any protection on the sheet? and can you please paste your code in code tags rather than post images.

 

[sublimus]

no. it's not protected.

 

[MARK858]

If you can, upload our workbook to a free file hosting site like www.box.com or www.dropbox.com.
Mark the file for sharing and post the link it provides in the thread.
Make sure that you amend any sensitive data before uploading.

 

[JLGWhiz]

How about

With sh.UsedRange
    .Value = .Value
End with

That should retain your formatting and eliminate the formulas.

 

[sublimus]

Hi:
I added that and seemed to bypass, but it's not letting me proceed on the
Next sh line. Says missing a For...but it's there.