search, copy and paste through multiple sheets

J

Jeff S.

I would like a macro that searches through the same column on multiple pages.
If the hyperlink text in the cell contains the search text it copies and
pastes the hyperlink cell to the main page. Any thoughts where to start?
 
S

smartin

Jeff said:
I would like a macro that searches through the same column on multiple pages.
If the hyperlink text in the cell contains the search text it copies and
pastes the hyperlink cell to the main page. Any thoughts where to start?
Click to expand...

Try the code below. This assumes some things you can figure out looking
at the constant declarations and that results should be pasted on sheet
Main in cell A1 and down.


' ----------------------------------- begin code
Sub CopyHyperlinks()

Const ColToSearch As String = "C:C"
Const SearchTerm As String = "*thing*"

Dim WKS As Worksheet
Dim MyCell As Range
Dim PasteCell As Range

Application.ScreenUpdating = False
Worksheets("Main").Range("A:A").Clear
Set PasteCell = Worksheets("Main").Range("A1")
For Each WKS In ActiveWorkbook.Worksheets
If WKS.Name <> "Main" Then
WKS.Activate
For Each MyCell In Application.Intersect _
(Range(ColToSearch), WKS.UsedRange)
If MyCell.Hyperlinks.Count > 0 And _
MyCell.Formula Like SearchTerm Then
MyCell.Copy
Worksheets("Main").Activate
PasteCell.Activate
ActiveSheet.Paste
Set PasteCell = PasteCell.Offset(1, 0)
End If
Next MyCell
End If
Next WKS
Application.ScreenUpdating = True
End Sub
' ------------------------------------- end code
 
Top