Solved

Remove links and formulas from Excel worksheets

Posted on 2014-12-15
22
409 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 32

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 32

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

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

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 32

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 32

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:Danny Child
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
 
LVL 32

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 32

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 32

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 32

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 69

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 32

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 69

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 69

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

813 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

12 Experts available now in Live!

Get 1:1 Help Now