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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I think I would just delete this
VBA Code:
With sh.UsedRange
.Cells.Copy
>>.Cells.PasteSpecial xlPasteValuesAndNumberFormats
.Cells(1).Select
End With
and replace it with this
VBA Code:
With sh
    .UsedRange.Copy
    .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End With
 

sublimus

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What do you get with the below?
VBA Code:
With sh.UsedRange
     .Copy
     .PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
     .Cells(1).Select
End With
 

sublimus

New Member
Joined
Jun 17, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do you have any protection on the sheet? and can you please paste your code in code tags rather than post images.
 

sublimus

New Member
Joined
Jun 17, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

no. it's not protected.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
How about
VBA Code:
With sh.UsedRange
    .Value = .Value
End with

That should retain your formatting and eliminate the formulas.
 

sublimus

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

Watch MrExcel Video

Forum statistics

Threads
1,130,059
Messages
5,639,816
Members
417,116
Latest member
Jakeyw

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