search, copy and paste through multiple sheets
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? |
search, copy and paste through multiple sheets
Jeff S. wrote:
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? 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 |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com