0

I have always used following to make a sheet to contain only values:

Sheets("NameOfTheTab").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:= xlPasteValues

But this is not safe, if someone/event change the selection, the program runs into random behaviour. How can can I get rid of this pattern?

vacip
  • 5,246
  • 2
  • 26
  • 54
NathaneilCapital
  • 1,389
  • 5
  • 17
  • 23
  • Only an event can cause confusion here, as user interaction with your workbook is disabled while your macro is running. – vacip Jul 06 '15 at 23:11
  • possible duplicate of [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – GSerg Jul 06 '15 at 23:34

2 Answers2

0

Well, for one thing, you can avoid selecting the cells, just directly copy them:

Sheets("NameOfTheTab").Activate
Cells.Copy
Cells(1,1).PasteSpecial Paste:= xlPasteValues

Although this might not be the fastest way, depending on your sheet/actual problem.

vacip
  • 5,246
  • 2
  • 26
  • 54
0

You should always avoid using select method, beacuse it is not reliable. The sub below is a sample to copy data from a worksheet and paste only values to another one. This sample sub assumes you are running this macro from your target workbook if not change ThisWorkbook to your target workbook.

Sub copy_paste_only_values()
'will copy all cells in your tab that contain data
ThisWorkbook.Worksheets("NameOfTheTab").Cells.Copy
'will paste only values to your target worksheet
ThisWorkbook.Worksheets("NameOfTheTargetTab").Range("A1")._
PasteSpecial Paste:=xlPasteValues
'empty the clipboard
Application.CutCopyMode = False
End Sub
noas
  • 71
  • 7