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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

ADVERTISEMENT

Thanks Peter. I appreciate the help.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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

ADVERTISEMENT

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
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I would suspect so, yes. Value2 would be even quicker.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,448
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top