|
Run Time error 1004 - Pastespecial method of range class failed.
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 |
Run Time error 1004 - Pastespecial method of range class failed.
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 -- Jacob "oli merge" wrote: 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 |
Run Time error 1004 - Pastespecial method of range class faile
Perfect, thanks for tidying up my messy recorded code too!
"Jacob Skaria" wrote: 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 -- Jacob "oli merge" wrote: 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 |
Run Time error 1004 - Pastespecial method of range class faile
Just to note though, I had to use "xlPasteAllUsingSourceTheme" to keep my
formatting (lots of bold and underlined words). Thanks, "oli merge" wrote: Perfect, thanks for tidying up my messy recorded code too! |
| All times are GMT +1. The time now is 11:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com