Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid paste error if nothing was copied.
I run three consecutive codes named consolidate 1, 2, then 3.
Using the example below, I get an error if the copy range has no data. What is the best way to rewrite this or somehow avoid the error if there is no data. I want it to simply move on to the next step if there's nothing to copy and or paste. Sub Consolidate1() Sheets("apples").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.Goto Selection.Cells(1) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid paste error if nothing was copied.
Post ALL of your code. It can be greatly simplified -- Don Guillett Microsoft MVP Excel SalesAid Software "J.W. Aldridge" wrote in message ... I run three consecutive codes named consolidate 1, 2, then 3. Using the example below, I get an error if the copy range has no data. What is the best way to rewrite this or somehow avoid the error if there is no data. I want it to simply move on to the next step if there's nothing to copy and or paste. Sub Consolidate1() Sheets("apples").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.Goto Selection.Cells(1) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid paste error if nothing was copied.
Sub Consolidate1()
Application.ScreenUpdating = False Sheets("apples").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.Goto Selection.Cells(1) End Sub _______________________________ Sub Consolidate2() Application.ScreenUpdating = False Sheets("cherries").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.Goto Selection.Cells(1) End Sub ____________________________ Sub Consolidate3() Application.ScreenUpdating = False Sheets("grapes").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.Goto Selection.Cells(1) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid paste error if nothing was copied.
Use error trapping to skip the paste if it fails:
Sub Consolidate1() Sheets("apples").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select On Error Resume Next ' <<==== Skip the next line if it errors out Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False On Error GoTo 0 ' <<==== Turn off error trapping Application.Goto Selection.Cells(1) End Sub HTH, Eric "J.W. Aldridge" wrote: I run three consecutive codes named consolidate 1, 2, then 3. Using the example below, I get an error if the copy range has no data. What is the best way to rewrite this or somehow avoid the error if there is no data. I want it to simply move on to the next step if there's nothing to copy and or paste. Sub Consolidate1() Sheets("apples").Select Range("a2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Summary").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.Goto Selection.Cells(1) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to avoid "insert copied cells"? | Excel Discussion (Misc queries) | |||
when I paste, instead of what I copied, I get an 'a'. | Excel Worksheet Functions | |||
How to avoid error? | Excel Programming | |||
How avoid errors when you Copy chartobjects paste in powerpoint | Excel Programming |