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

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

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