ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   copy and paste error (https://www.excelbanter.com/excel-programming/338605-copy-paste-error.html)

John
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

Tom Ogilvy
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

Dave Peterson
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

John
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

Tom Ogilvy
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

John
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


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com