Excelのこととか色々

Excel のこととか楽天とか いろいろ書いてみます・・・

【Excel/VBA 05】Copy and Paste Value Macro

   f:id:tuna-kichi:20200223230814p:plain

 

 

Show result of "COUNTIF" function

COUNTIF function re-calculates every time when the sheet refresh, so that makes your computer slow if the function is written in many cells.

 

One of the solutions is that copy the cells and do "Paste value".

But, I don't like the operation.

 

So, I made a macro to do it with one click.

Macros

※Write this macro in "Module".

********************************************
Sub
 CopyAndPasteValue()
 Selection.Copy 'Copy selected cells.
 Selection.PasteSpecial Paste:=xlPasteValues 'Paste value in same place
 Application.CutCopyMode = False 'Disable copy mode

End Sub

********************************************

 

Set the macro to the ribbon.

  

  Set the macro to the Ribbon

 

 

How to use

 

サンプル

1.Select cells where shows the result of COUNTIF function. (Cell J4 ~ J13)

2.Write COUNTIF function
  =COUNTIF($H$4:$H$13,H4)
  ※Fix the range by putting "$".

3.Press Ctrl + Enter after inputs ")" to copy the function to the selected cells.

4.Click the macro that you set on the ribbon.


  Set the macro to the Ribbon

Done.

 

 

This macro gives short break for my left little finger because I do copy and paste using a short-cut key many times.

 

**********************************************************

 

 About personal macro workbook

 

www.tuna-kichi.com

 

 About setting macros on the ribbon

www.tuna-kichi.com

 

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/