Excel VBA 2010 - Copy to WB, RTE 1004 - That command cannot be used on multiple selections

Dear all,

I have an issue in my Code.
Sometimes it runs without issues, and sometimes not. I have not found the system.

I want to copy a named area in a sheet in current wb (XLSM) to a new workbook. As default, new wb's are xlsx.
I have read somewhere, that this error can occur, if the new wb is xls. But that is not the case.

In the worksheet there are som hidden columns, but these are not part if the named area.

When an issue occurs, it is the same place. Please see the red line below.

Sub MailAnalysisSub()

Application.ScreenUpdating = False

Dim TempWorkbook As Workbook
Dim Tempsht As Worksheet

'----------
'Create new Workbook
Set TempWorkbook = Workbooks.Add
FolderPath = Application.ThisWorkbook.Path & "\14.ShipReport_Controller" & "\14.ShipReport_SentReportingPackages\"
Application.DisplayAlerts = False

TempWorkbook.Activate
Set Tempsht = Worksheets.Add
Tempsht1 = ActiveSheet.Name
ActiveSheet.Name = "Tempsht1"

'------------
ThisWorkbook.Worksheets("Input_Risk").Activate
ThisWorkbook.Worksheets("Input_Risk").Unprotect
Range("PrintArea_NotPaymentRisk").Select

Selection.Copy

TempWorkbook.Activate
Sheets("Tempsht1").Activate
Cells(1, 1).Activate

Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.Name = "PrintArea_NotPaymentRisk"

Application.CutCopyMode = False



...more code.......

I hope you can shed som light over this issue - what am I doing wrong, and most exciting, why is this not a consequence error. Sometimes it works, sometimes not.

wbr
Benjamin

Hi, Sleever,

please have a read at Forum Rule #3 and add code-tags to your procedure as explained there.

Ciao,
Holger

Even easier to add code tags which make the code easier to read and to copy/paste.

Anyway, does this work?

Range("PrintArea_NotPaymentRisk").Copy 

With Tempsht.Cells(1,1)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

Hi Norie,

yes - you are right. I have appologized to Holger.
I have tried your code. I get error her;

Sub MailAnalysisSub()

'---------------------------------------
'Mail the analysis to the end intepreter
'Area=PrintArea_PaymentRisk
'---------------------------------------

Application.ScreenUpdating = False

Dim TempWorkbook As Workbook
Dim Tempsht As Worksheet

RightHereRightNow = ActiveCell.Address
RightHereRightNow_Sheet = ActiveSheet.Name

'----------
'Create new Workbook
Set TempWorkbook = Workbooks.Add
FolderPath = Application.ThisWorkbook.Path & "\14.ShipReport_Controller" & "\14.ShipReport_SentReportingPackages\"
Application.DisplayAlerts = False

TempWorkbook.Activate
Set Tempsht = Worksheets.Add
Tempsht1 = ActiveSheet.Name
ActiveSheet.Name = "Tempsht1"

'------------
ThisWorkbook.Worksheets("Input_Risk").Activate
ThisWorkbook.Worksheets("Input_Risk").Unprotect

Range("PrintArea_NotPaymentRisk").Copy

With Tempsht.Cells(1, 1)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

'Range("PrintArea_NotPaymentRisk").Select
'
'Selection.Copy
'
'TempWorkbook.Activate
'Sheets("Tempsht1").Activate
'Cells(1, 1).Activate
'
'Selection.PasteSpecial Paste:=xlPasteValues
'Selection.PasteSpecial Paste:=xlPasteFormats

Excel error: Run-time errot '1004':
PasteSpecial method of Range class failed.


wbr
Ben

Hi, Ben,

you should try and work with objects throughout (I know: easy to tell and hard to do).

Maybe try

Range("PrintArea_NotPaymentRisk").Copy Tempsht.Cells(1, 1)

With Tempsht.Cells(1, 1).CurrentRegion
  .Value = .Value
  :Name = "PrintArea_NotPaymentRisk"
End With

instead of

Range("PrintArea_NotPaymentRisk").Copy

With Tempsht.Cells(1, 1)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

Ciao,
Holger