| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste error
Could someone help me with why I get an error when I run this simple code to
copy and paste... THANKS! Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and paste error
when you clear you range, you probably also empty the clipboard. Try
changing the order. Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub Sub CopyDate dim rng as Range, rng1 as Range set rng = Sheets("dump").Range("A2").currentRegion if rng.row = 1 then rng.Offset(1,0).Resize(rng.rows.count-1) _ .ClearContents end if set rng1 = Sheets("Data").Range("A2").CurrentRegion if rng1.row = 1 then set rng1 = rng1.Offset(1,0).Resize(rng1.rows.count-1) end if rng1.copy rng(1).PasteSpecial xlValues End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Could someone help me with why I get an error when I run this simple code to copy and paste... THANKS! Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and paste error
Is this in a general module?
I'm guessing yes. this line: Sheets("dump").Cells("A2").PasteSpecial xlPasteValues should be: Sheets("dump").Range("A2").PasteSpecial xlPasteValues (replace .cells() with .range().) But when I ran your code, this line: Selection.ClearContents Cleared the clipboard for me. That meant that there was nothing to paste. You could rearrange your code so that it clears the contents before it does the copy. If this code is behind a worksheet module, then more needs to be done. John wrote: Could someone help me with why I get an error when I run this simple code to copy and paste... THANKS! Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and paste error
I got an type mismatch error then changed one other thing and it worked...
here it is... Thanks Tom Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select ActiveSheet.Paste Range("A2").Select End Sub "Tom Ogilvy" wrote: when you clear you range, you probably also empty the clipboard. Try changing the order. Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub Sub CopyDate dim rng as Range, rng1 as Range set rng = Sheets("dump").Range("A2").currentRegion if rng.row = 1 then rng.Offset(1,0).Resize(rng.rows.count-1) _ .ClearContents end if set rng1 = Sheets("Data").Range("A2").CurrentRegion if rng1.row = 1 then set rng1 = rng1.Offset(1,0).Resize(rng1.rows.count-1) end if rng1.copy rng(1).PasteSpecial xlValues End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Could someone help me with why I get an error when I run this simple code to copy and paste... THANKS! Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and paste error
I missed the CELLS vice RANGE in your original code. Sorry about that.
If you still want to do a PasteSpecial Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Sheets("dump").Range("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub -- Regards, Tom Ogilvy "John" wrote in message ... I got an type mismatch error then changed one other thing and it worked... here it is... Thanks Tom Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select ActiveSheet.Paste Range("A2").Select End Sub "Tom Ogilvy" wrote: when you clear you range, you probably also empty the clipboard. Try changing the order. Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub Sub CopyDate dim rng as Range, rng1 as Range set rng = Sheets("dump").Range("A2").currentRegion if rng.row = 1 then rng.Offset(1,0).Resize(rng.rows.count-1) _ .ClearContents end if set rng1 = Sheets("Data").Range("A2").CurrentRegion if rng1.row = 1 then set rng1 = rng1.Offset(1,0).Resize(rng1.rows.count-1) end if rng1.copy rng(1).PasteSpecial xlValues End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Could someone help me with why I get an error when I run this simple code to copy and paste... THANKS! Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and paste error
I see... thanks to both of you.
"Dave Peterson" wrote: Is this in a general module? I'm guessing yes. this line: Sheets("dump").Cells("A2").PasteSpecial xlPasteValues should be: Sheets("dump").Range("A2").PasteSpecial xlPasteValues (replace .cells() with .range().) But when I ran your code, this line: Selection.ClearContents Cleared the clipboard for me. That meant that there was nothing to paste. You could rearrange your code so that it clears the contents before it does the copy. If this code is behind a worksheet module, then more needs to be done. John wrote: Could someone help me with why I get an error when I run this simple code to copy and paste... THANKS! Sheets("DATA").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("dump").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents Sheets("dump").Cells("A2").PasteSpecial xlPasteValues Range("A2").Select End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy paste error | Charts and Charting in Excel | |||
| copy and paste error | Excel Discussion (Misc queries) | |||
| copy-paste error | Excel Programming | |||
| Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
| Error in Copy/Paste | Excel Programming | |||
Linear Mode
