0

I have a script that takes an excel workbook and turns all formulas into values. I have used it successfully in the past, but am receiving the following error on spreadsheets I am need to convert to values:

Run-time error '1004': Method 'Select' of object 'Sheets' failed

Debug stops on the first line Worksheets.Select

...
Worksheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Select
Application.CutCopyMode = False

Given the fact that this script does work on other workbooks I have a few ideas on why I get the error. In the workbooks with errors I have hidden sheets. That may be part of the issue. I need this to work on workbooks with hidden sheets. Thank you for your ideas and suggestions on troubleshooting this.

Community
  • 1
  • 1
Mitch
  • 554
  • 2
  • 17
  • Do a loop to unhide them first or ignore them? Can't tell which you want XD – findwindow Aug 24 '15 at 15:54
  • You need to identify the worksheet you're talking about. ie: worksheets(1).Select; or worksheets("Sheet1").Select. Also, try not to use Select at all. – Grade 'Eh' Bacon Aug 24 '15 at 15:55
  • 2
    Take a look at the general thinking around using select. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – MatthewD Aug 24 '15 at 16:00
  • Ideally I would like to remove formulas from entire workbook, including hidden sheets. I will take a look at using select less. MatthewD, you link is helpful. – Mitch Aug 24 '15 at 16:21

2 Answers2

1

This is the equivalent of yours Worksheet.Select, but only the visible ones

Dim item As Worksheet

For Each item In Worksheets
    If item.Visible Then
      item.Select
    End If
Next

But as the other users have commented, you should avoid the .Select funcion.

Jaime Mendes
  • 643
  • 3
  • 9
  • This should work, but Excel Developers answer is simpler and doesn't use .Select (as mentioned above). Thanks! – Mitch Aug 24 '15 at 18:46
1
dim ws as Worksheet

For Each ws in ThisWorkbook.Worksheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next
Excel Developers
  • 2,785
  • 2
  • 21
  • 35
  • This seems to be working for both visible and hidden sheets. It is much simpler, but I don't know that I fully understand the code. I understand the definition of UsedRange, but the logic is saying take the values in the used range and set the cells value equal to the values in the used range? Am I understanding this correclty? It seems you are telling the cells to equal the same thing. I guess that is why you have .value? – Mitch Aug 24 '15 at 17:57
  • When a cell contains a formula, the Formula property of the cell (in VBA) returns a string containing the formula that you will see in the Formula bar in Excel when the cell is active. The Value property returns the result of the formula, as it appears in the cell. So by setting the Value property (the left hand side) equal to the Value property (on the right hand side) we are overwriting the Formula currently in the cell with the result of the formula. Hope that makes sense. – Excel Developers Aug 24 '15 at 20:02
  • Yes, that makes sense. Clever, yet simple! Thanks for the answer! – Mitch Aug 25 '15 at 22:47