マクロをどんな Excel ファイルにも実行できるように設定して、3秒かかる操作も1秒で完了。あといろいろ書いてみます・・・

【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







PVアクセスランキング にほんブログ村 Excelのこととか色々 - にほんブログ村 にほんブログ村 ライフスタイルブログ 普通の暮らしへ にほんブログ村 英語ブログ 初心者英語へ にほんブログ村 IT技術ブログ VBAへ