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
|