Run Time error 1004 - Pastespecial method of range class failed.

[oli merge]

Hi,

I am trying to programmatically copy the text contents of one cell
(including formatting) on one worksheet to a merged range on another.

I get the error in the title when I run the macro initially... then
strangely it works with no error after failing to run it a couple of times.

My code is as follows:

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("admin").Select
Range("A2").Select
Selection.Copy
Sheets("Submission Form").Select
Range("D6:G8").Select
End With
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme,
Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Jacob Skaria]

Try the below..

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("admin").Range("A2").Copy
Sheets("Submission Form").Range("D6:G8").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

With Selection
.BorderAround (xlDouble)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[oli merge]

Perfect, thanks for tidying up my messy recorded code too!

 

[oli merge]

Just to note though, I had to use "xlPasteAllUsingSourceTheme" to keep my
formatting (lots of bold and underlined words).

Thanks,