Remove links and formulas from Excel worksheets

We have several final report sheets that obtain values from links to a master data sheet.
We copy and paste the values to break the links using the following VBA Code.

There must be a more efficient way remove links and formulas from an Excel Workbook.

in this example all of the hidden sheets are deleted later using VBA code.

If Sheets("ONE").Visible = True Then
    cut_paste_values ("ONE")
End If
If Sheets("TWO").Visible = True Then
    cut_paste_values ("TWO")
End If   

Open in new window

Allen PrinceTraining DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
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
0
Allen PrinceTraining DeveloperAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
"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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Allen PrinceTraining DeveloperAuthor Commented:
yes the sytax is tricky that is why we are asking the "Experts"
0
Rob HensonFinance AnalystCommented:
Posted the syntax on your other question.
0
Allen PrinceTraining DeveloperAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
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
0
Allen PrinceTraining DeveloperAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
You can do a loop through each sheet in a workbook without specifying sheet name.
0
Allen PrinceTraining DeveloperAuthor Commented:
yes that will work..
first one to post code that works gets the 500
0
Danny ChildIT ManagerCommented:
If you're essentially trying to make this sheet read-only, why not just Save As.. PDF.
Or lock and protect cells?
0
Rob HensonFinance AnalystCommented:
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

Open in new window

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
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
0
Rob HensonFinance AnalystCommented:
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

Open in new window

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

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
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

Open in new window

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

Thanks
Rob H
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Don't use Select (it is bad style anyway), and the issue with hidden sheets vanishes.
The code is also kind of cumbersome. This should be all needed:
Sub CopyPasteValues()
  For Each Worksheet In ActiveWorkbook.Worksheets
    With Worksheet
      .UsedRange.Copy
      .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False
    End With
  Next Worksheet
  Application.CutCopyMode = False
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Nice, I tried doing something similar but it was only dealing with the first worksheet. I note now that you have used a With statement for the worksheet, I think that is what I was missing so had to scroll through selecting each one. As you say, using Select is bad form and I usually try to avoid it.

Thanks
Rob H
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

Open in new window

works the same (and is even shorter :-) ).
0
Allen PrinceTraining DeveloperAuthor Commented:
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

Open in new window

0
Allen PrinceTraining DeveloperAuthor Commented:
Rob worked real hard on this but Qlemo Nailed it!
Thanks 10^6 you guys!
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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 ;-).
0
Allen PrinceTraining DeveloperAuthor Commented:
you are right I set option explicit, could have mentioned that, thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.