How do you copy and paste cell value only with VBA in Excel?

I was recently asked this question: Is there a way in Excel to write a macro to copy a cell's numeric value only and not the formula? Basically, can you use VBA to paste only the value, like the Paste Special feature in the Values Only menu? The answer is yes!

To do a simple Paste operation you might write this line of code:
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("A2")

Instead of '.Paste', use '.PasteSpecial." Try it with this line:
Worksheets("Positions").Range("A2").PasteSpecial xlPasteValues

If you still have questions then see the Microsoft Excel VBA help file for the use of the PasteSpecial method. It's pretty straightforward.

Another suggestion, instead of using code like this:

Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("A2")


you could use something like this:

Worksheets("Positions").Range("W2").value = .Range("A3").End(xlDown).Offset(0, 41).value

This new code will accomplish the same thing as the original but it will also allow your code to run more quickly by avoiding the use of copy and paste althogether.

Where can I download the roommate's shared apartment expenses spreadsheet?

My shared apartment expenses spreadsheet has become very popular and I have received a lot of positive feedback. Many people have emailed me or commented on this blog and I've sent each request a copy of the spreadsheet. Still, I get the feeling that a lot of readers are a bit apprehensive about posting their email addresses here. I would never give that information away but anyone reading the comments could. Therefore, I have finally decided to find a site where I can post my Excel files for easy download. Here is the link for the latest version of my shared apartment expenses spreadsheet for those of you with roommates. In the coming days I will be posting more of my templates. Any requests?

Shared Apartment Expenses Spreadsheet.xls

Some lesser known keyboard shortcuts in Microsoft Excel

Here are a few shortcuts in Microsoft Excel that you may not be familiar with:

Alt+F11:Open VBE
Ctrl+Shift+Enter:Array formula
Ctrl+F3:Define name
F3:Paste name
Ctrl+Spacebar:Select columns
Shift+Spacebar:Select rows
Ctrl+1:Format cells
Ctrl+B:Bold
Ctrl+U:Underline
Ctrl+:Current date
Ctrl+shift+:Current time

File this one under "what are some keyboard shortcuts in excel?"

Where can I download an Apartment Search Spreadsheet ?

My apartment search spreadsheet has become very popular and I have received a lot of positive feedback. Many people have emailed me or commented on this blog and I've sent each request a copy of the spreadsheet. Still, I get the feeling that a lot of readers are a bit apprehensive about posting their email addresses here. I would never give that information away but anyone reading the comments could. Therefore, I have finally decided to find a site where I can post my Excel files for easy download. Here is the link for the latest version of my apartment search spreadsheet. In the coming days I will be posting more of my templates. Any requests?

Apartment Search.xls