Hardcode links - loop through sheets

[Steph]

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]

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)

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

 

[Steph]

Thanks Dave!!

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)

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