Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy & Paste Formula | Excel Discussion (Misc queries) | |||
Search in Multiple Sheets | Excel Discussion (Misc queries) | |||
Search, copy and paste help | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
VBA-code for search,copy and paste | Excel Discussion (Misc queries) |