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

【Excel/VBA 05】Copy and Paste Value Macro




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.


※Write this macro in "Module".

 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
  ※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




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




 About setting macros on the ribbon