View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] c1802362@cox.net is offline
external usenet poster
 
Posts: 17
Default Please explain copy/pastespecial issue

Can anyone explain the following problem:

If I run the following code

Sub Demo()

Dim shtSource As Worksheet
Dim shtTarget As Worksheet

Set shtTarget = Sheets("input")
Set shtSource = Sheets("output")

shtSource.Range("A1").CurrentRegion.Copy

With shtTarget
.Cells.Clear
.Range("A1").PasteSpecial
End With

End Sub

I get "Run time error 1004:

PasteSpecial method of Range class failed"

If I run the code as follows, I get no error and everything is fine:

Sub Demo()

Dim shtSource As Worksheet
Dim shtTarget As Worksheet

Set shtTarget = Sheets("input")
Set shtSource = Sheets("output")

shtTarget.Cells.Clear
shtSource.Range("A1").CurrentRegion.Copy

With shtTarget
.Range("A1").PasteSpecial
End With

End Sub

To my way of thinking it makes no sense to be bouncing between the source and target objects. However, what I think is logical is bombing. Can one of the experts enlighten me?

Art