Used Range copy Paste Values

[jomili]

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:

'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

 

[VoG]

Try

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

 

[jomili]

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?

 

[VoG]

No, it has to go on two lines.

 

[jomili]

Thanks Peter. I appreciate the help.

 

[RoryA]

You can assign the value of one to the other as long as you size both ranges the same.

 

[jomili]

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]

Like so:

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]

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]

I would suspect so, yes. Value2 would be even quicker.