Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hardcode links - loop through sheets
Hello. Below I have a bit of code that hardcodes links to external
workbooks within the ActiveSheet. Can someone please modify this to loop through all sheets in ThisWorkbook? Thank you! Sub Hardcode() Dim ws As Worksheet On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If rng = Empty Then MsgBox ("No Links in Sheets") Else For Each cell In rng sform = cell.Formula If InStr(sform, "[") Then cell.Formula = cell.Value End If Next End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hardcode links - loop through sheets
Sub Hardcode2()
Dim ws As Worksheet Dim cell as range dim rng as range dim sForm as string For each ws in activeworkbook.worksheets set rng = nothing On Error Resume Next Set rng = ws.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If rng is nothing Then MsgBox "No Links in: " & ws.name Else For Each cell In rng sform = cell.Formula If InStr(sform, "[") Then cell.Formula = cell.Value End If Next cell End If next ws End Sub (Watch out for typos) Steph wrote: Hello. Below I have a bit of code that hardcodes links to external workbooks within the ActiveSheet. Can someone please modify this to loop through all sheets in ThisWorkbook? Thank you! Sub Hardcode() Dim ws As Worksheet On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If rng = Empty Then MsgBox ("No Links in Sheets") Else For Each cell In rng sform = cell.Formula If InStr(sform, "[") Then cell.Formula = cell.Value End If Next End If End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hardcode links - loop through sheets
Thanks Dave!!
"Dave Peterson" wrote in message ... Sub Hardcode2() Dim ws As Worksheet Dim cell as range dim rng as range dim sForm as string For each ws in activeworkbook.worksheets set rng = nothing On Error Resume Next Set rng = ws.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If rng is nothing Then MsgBox "No Links in: " & ws.name Else For Each cell In rng sform = cell.Formula If InStr(sform, "[") Then cell.Formula = cell.Value End If Next cell End If next ws End Sub (Watch out for typos) Steph wrote: Hello. Below I have a bit of code that hardcodes links to external workbooks within the ActiveSheet. Can someone please modify this to loop through all sheets in ThisWorkbook? Thank you! Sub Hardcode() Dim ws As Worksheet On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If rng = Empty Then MsgBox ("No Links in Sheets") Else For Each cell In rng sform = cell.Formula If InStr(sform, "[") Then cell.Formula = cell.Value End If Next End If End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Y Axis : Dynamically Hardcode Min/Max Values? | Charts and Charting in Excel | |||
hardcode a column in a formula | Excel Worksheet Functions | |||
Hardcode Links - edit my code please? | Excel Programming | |||
Change hardcode to ....? | Excel Programming | |||
Loop across Sheets and number of sheets | Excel Programming |