PasteSpecial method of Range class failed

[Chip]

I have written a procedure to use copy/paste special for several dozen cells
individually.
In two cases, the destination cell is merged so I unmerge before pasting
then remerge.
In one sub procedure it orks fine in another I get the error in the subject
line.
Here is a snippet of the code

If ActiveCell.Offset(0, 42) <> "" Then
ActiveCell.Offset(0, 42).Copy
Sheets("Application Increases").Select
Range("U8:AD8").MergeCells = False
Range("U8").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("U8:AD8").MergeCells = True

I have tried using activecell in place of selection and also tried adding a
line to set cutcopymode=false.
Any thoughts?

 

[Andy Smith]

How about a one-liner that doesn't use the *Windows* clipboard?

If ActiveCell.Offset(0, 42) <> "" Then Sheets("Application Increases").Value
= ActiveCell.Offset(0, 42).Value

Better is:

If Len(ActiveCell.Offset(0, 42).Text) > 0 Then Sheets("Application
Increases").Value = ActiveCell.Offset(0, 42).Value

 

[Don Guillett]

Just change your sheet name and destination range. Notice the DOTS " .
"

Sub copytomerged()
With Sheets("sheet12").Range("f25:g25")
.MergeCells = False
If ActiveCell.Offset(, 4) <> "" Then
ActiveCell.Offset(, 4).Copy .Range("a1")
End If
.MergeCells = True
End With
end sub

 

[Chip]

Andy and Don,
Thanks both for the quick responses.
I actually wotked it out another way since it worked on two sheets and
failed on two.
My error was occuring inside a loop and the successful sheets did not have
that loop.
I actually leave the cells unmerged from a step external to the loop then do
a final mergecells after the loop ends.
I will definitely try both of your solutions for future use.

 

[Don Guillett]

Good. But, AFAIK you need to add the first cell of the destination range.

If ActiveCell.Offset(0, 42) <> "" Then _
Sheets("Application Increases").range("u8").Value _
= ActiveCell.Offset(0, 42).Value

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

How about a one-liner that doesn't use the *Windows* clipboard?

If ActiveCell.Offset(0, 42) <> "" Then _
Sheets("Application Increases").range("u8").Value _
= ActiveCell.Offset(0, 42).Value