| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
| Run-time error '1004': PasteSpecial method of Range class failed | Excel Programming | |||
| Run-Time error '1004' : Select method of Range class failed | Excel Discussion (Misc queries) | |||
| runtime error 1004 pastespecial method of range class failed | Excel Programming | |||
| Run-time error '1004' PasteSpecial Method of Range Class Failed | Excel Programming | |||
Linear Mode
