How to update all links before re-calculation for macro?

E

Eric

I have a macro to update the links for 5 worksheets to internet site,
whenever I run this macro, which will automatically update the links for all
worksheets.
After one link is updated, it will re-calculate all worksheet before
updating the second link. Would it be possible that all worksheets will be
re-calculated only after all links are updated.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
 
J

Jarek Kujawa

I use the following (if there is not so many cells with hyperlinks/
formulae)

in each worksheet define a named range, named e.g. Sheet1!link, Sheet2!
link etc.

then use the following macro

Sub Aktualize()

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range(ws.Name & "!" & "link").Select
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = cell.Formula
End If
Next cell
Next ws

End Sub


if there are numerous hyperlinks then I cannot see other solution than
to parse each cell in UsedRange, check whether is HasFormula etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top