Solved

Remove links and formulas from Excel worksheets

Posted on 2014-12-15
22
371 Views
Last Modified: 2014-12-17
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

0
Comment
Question by:AMixMaster
  • 10
  • 8
  • 3
  • +1
22 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40500719
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
 

Author Comment

by:AMixMaster
ID: 40500841
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40500854
"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
 

Author Comment

by:AMixMaster
ID: 40500893
yes the sytax is tricky that is why we are asking the "Experts"
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40500911
Posted the syntax on your other question.
0
 

Author Comment

by:AMixMaster
ID: 40500913
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40500923
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
 

Author Comment

by:AMixMaster
ID: 40501138
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40501167
You can do a loop through each sheet in a workbook without specifying sheet name.
0
 

Author Comment

by:AMixMaster
ID: 40501197
yes that will work..
first one to post code that works gets the 500
0
 
LVL 23

Expert Comment

by:DanCh99
ID: 40502163
If you're essentially trying to make this sheet read-only, why not just Save As.. PDF.
Or lock and protect cells?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40502285
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40502310
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40502341
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40502346
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
 
LVL 68

Accepted Solution

by:
Qlemo earned 400 total points
ID: 40504708
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
ID: 40504720
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40504728
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
 

Author Comment

by:AMixMaster
ID: 40505528
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
 

Author Closing Comment

by:AMixMaster
ID: 40505535
Rob worked real hard on this but Qlemo Nailed it!
Thanks 10^6 you guys!
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505700
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
 

Author Comment

by:AMixMaster
ID: 40505981
you are right I set option explicit, could have mentioned that, thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now