Used Range copy Paste Values

jomili

New Member
Joined
Dec 15, 2009
Messages
33
I'm trying to do something that should be fairly simple, but I'm having trouble. What I want is to copy the Used Range from my View1 sheet and paste only the values into my View1Pivot sheet. Here's the code I'm trying to use:
Code:
'Copy the View1 Information
    UPDT.Sheets("View1").UsedRange.Value.Copy Destination:=Tool.Sheets("View1Pivot").Cells(1, 1)
If I take out "Value" then the code works just fine at copying and pasting, but how do I change it to only give me the values?
Thanks,
John
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
UPDT.Sheets("View1").UsedRange.Copy
Tool.Sheets("View1Pivot").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
 

jomili

New Member
Joined
Dec 15, 2009
Messages
33
VoG,

That one works just fine. I guess there's not a way to do it as a one-liner, using the "Copy Destination:" routine?
 

jomili

New Member
Joined
Dec 15, 2009
Messages
33
Thanks Peter. I appreciate the help.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You can assign the value of one to the other as long as you size both ranges the same.
 

jomili

New Member
Joined
Dec 15, 2009
Messages
33
How do you do that? One range (copied from) has a size, the range of the "UsedRange", but the other is a blank sheet. How do you "size" that range?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Like so:
Code:
With UPDT.Sheets("View1").UsedRange
   Tool.Sheets("View1Pivot").Cells(1, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
Re your reported post, we don't mark threads solved here - this isn't ExcelForum. (it's working for a start. ;))
 

jomili

New Member
Joined
Dec 15, 2009
Messages
33
Thanks Rory,

Would the resizing type routine run faster than the copy/paste routine? It seems like it would, because it bypasses the clipboard. I'm stringing a lot of operations together, so the faster routines are very much appreciated.

Thanks for letting me know about not marking the posts 'Solved'. I'll try to remember that next time I'm here.

Thanks,
John
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I would suspect so, yes. Value2 would be even quicker.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,789
Messages
5,446,504
Members
405,404
Latest member
clead

This Week's Hot Topics

Top