Remove links and formulas from Excel worksheets

On the Data Tab, select Edit Links - Break Links.

This will convert all formulas that include links to values. It does not break the link for Named Ranges.

Thanks
Rob H

We need to inlude this in VBA code within a module.
Having the user select a menu item is not an option.  
It works as is however I there must be a method to copy and paste without naming the individual sheets.

"Break Links" will break links on all sheets in the current file but it will leave any internal formulas intact.

So, if you have a range of cells all with links to external files and then a sum of that range. Breaking links will convert the links to values but will leave the sum as a formula; unlike copying and pasting the whole sheet as values.

The Break Links option can be included in a VBA script but the syntax can be a bit tricky.

Thanks
Rob

yes the sytax is tricky that is why we are asking the "Experts"

Posted the syntax on your other question.

I have asked about specifically breaking links in another question,

What is needed for this example is a way to copy and paste all values from several sheets without naming each sheet
like this:
for all sheets that are not hidden
or
for all sheets in the workbook hidden or not
copy paste values

I am not sure I see the difference.

In this example are you wanting to overwrite all formulas with values, even those without external links?

Thanks
Rob H

Yes, I want to copy and paste values for all of the sheets in the workbook.

When I tried it before there was an error if some sheet was hidden.
The way it is now is ok because the code deletes all of the hidden sheets at the end before deleting itself.
I want to be able to use the code in different workbooks with different sheet names without going through and changing the sheet names in the code

You can do a loop through each sheet in a workbook without specifying sheet name.

yes that will work..
first one to post code that works gets the 500

If you're essentially trying to make this sheet read-only, why not just Save As.. PDF.
Or lock and protect cells?

Try this for starters, not sure how it will cope with hidden sheets.

Sub CopyPasteValues()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Cells.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Next wks
End Sub

Select allOpen in new window

Thanks
Rob H

Apologies, posted without testing. Doesn't move on to next worksheet for some reason and doesn't like hidden sheets.

I will keep trying.

Thanks
Rob H

Sorted:

Sub CopyPasteValues()

With ActiveWorkbook.Worksheets
    For Each Worksheet In ActiveWorkbook.Worksheets
        ShtName = Worksheet.Name
        Sheets(ShtName).Select
        Cells.Copy
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
    Next Worksheet
End With
End Sub

Select allOpen in new window

Still doesn't like the hidden sheets. What do you want to do with those?

Thanks
Rob H

Amended to skip hidden sheets:

Sub CopyPasteValues()

For Each Worksheet In ActiveWorkbook.Worksheets
    Shtname = Worksheet.Name
    If Sheets(Shtname).Visible = True Then
    Sheets(Shtname).Select
    Cells.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Else
' Do something else
    End If
Next Worksheet
End Sub

Select allOpen in new window

Also found that didn't need to wrap in a With statement so removed those lines.

Thanks
Rob H

You don't need the With, but you need to use the Worksheet var reference:

Sub CopyPasteValues()
  For Each Worksheet In ActiveWorkbook.Worksheets
    Worksheet.UsedRange.Copy
    Worksheet.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False
  Next Worksheet
  Application.CutCopyMode = False
End Sub

Select allOpen in new window

works the same (and is even shorter :-) ).

Added Dim and needed With to make it work

Sub CopyPasteValues()

    Dim WS As Worksheet
  For Each WS In ActiveWorkbook.Worksheets
    With WS
      .UsedRange.Copy
      .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False
    End With
  Next WS
  Application.CutCopyMode = False
  
End Sub

Select allOpen in new window

Rob worked real hard on this but Qlemo Nailed it!
Thanks 10^6 you guys!

Those changes in http:#a40505528 are not needed. My code in http:#a40504728 works fine. If you have set Option Explicit, then you need to Dim the For variable, that much is true, but you didn't tell us you did before ;-).

you are right I set option explicit, could have mentioned that, thanks