• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COpying Hyperlinks along with sorted/Searched data using Macros in MS Excel


New Member

i have written the following code , which searchs through various columns and basically filters my data and then copies it to the worksheet, but i am facing one problem, the source data has a columns containing hyper links to various other files on the system, but the copied data does not copy the hyperlink, i have to give a presentation at my college and need to sort this problem out by tommorrow afternoon, please help me out with the same.

The code is '

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCrit As Range

Set rngCrit = wksCrit.Range("CriteriaRng")

Application.EnableEvents = False

Select Case Target.Address

Case Range("SelReg").Address

rngCrit.Cells(2, 1).Value = Target.Value

Case Range("Selcountry").Address

rngCrit.Cells(2, 2).Value = Target.Value

Case Range("SelCount").Address

rngCrit.Cells(2, 3).Value = Target.Value

Case Range("SelCity").Address

rngCrit.Cells(2, 4).Value = Target.Value

Case Range("SelDate").Address

rngCrit.Cells(2, 5).Value = Target.Value

Case Range("WhHotN").Address

rngCrit.Cells(2, 6).Value = Target.Value

Case Range("WhResN").Address

rngCrit.Cells(2, 7).Value = Target.Value

Case Range("WhOffN").Address

rngCrit.Cells(2, 8).Value = Target.Value

Case Range("WhRetN").Address

rngCrit.Cells(2, 9).Value = Target.Value

End Select

If Range("SelReg").Value = "" Then

rngCrit.Cells(2, 1).ClearContents

End If

If Range("Selcountry").Value = "" Then

rngCrit.Cells(2, 2).ClearContents

End If

If Range("SelCount").Value = "" Then

rngCrit.Cells(2, 3).ClearContents

End If

If Range("SelCity").Value = "" Then

rngCrit.Cells(2, 4).ClearContents

End If

If Range("SelDate").Value = "" Then

rngCrit.Cells(2, 5).ClearContents

End If

If Range("WhHotN").Value = "" Then

rngCrit.Cells(2, 6).ClearContents

End If

If Range("WhResN").Value = "" Then

rngCrit.Cells(2, 7).ClearContents

End If

If Range("WhOffN").Value = "" Then

rngCrit.Cells(2, 8).ClearContents

End If

If Range("WhRetN").Value = "" Then

rngCrit.Cells(2, 9).ClearContents

End If

If Not rngCrit Is Nothing Then

wksResRep.Range("B1:W65").AdvancedFilter _

Action:=xlFilterCopy, _

CriteriaRange:=rngCrit, _

CopyToRange:=Range("ExtractDetails"), Unique:=False

End If


Application.EnableEvents = True

Exit Sub


Resume exitHandler

End Sub

Please reply back soon.



New Member
but the data will keep on increasing and changing..so the range will keep on changing..

Thanks a lot for reply 'Hui'

Please find me a solution to the above problem


Excel Ninja
Staff member
2 more questions

What criteria is in the CriteriaRange:=rngCrit ?

When I run that on Excel 2010 it appears to be ok, do you have access to 2010?


New Member
No, actually i have to do it in Excel 2003 only, that is the restriction from the university side.

There are more than one criterias

i have to sort it by city, country, region, report source, date, type (residential, hotel and so on..)

r u sure that it is working in excel 2010 ?


Excel Ninja
Staff member
I can't fully test as I don't have the Data and Named Ranges which you have setup.

But when I run this bit

wksResRep.Range("B1:W65").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=rngCrit, _
CopyToRange:=Range("ExtractDetails"), Unique:=False
with data that includes a web link and some basic criteria it copies the range and filters according to the criteria and copies the links ok

Is there anything in the "WhHotN" range which is causing the links to be left out ?

Is the "ExtractDetails" range formatted as normal text. So in fact it is working but appears like it isn't.


New Member
Hi.. Hui..

it is still not working and i am tense..now..

there is nothing to do with "WhHotN" its just a searching criteria which i named so.

the "ExtractDetails" range is formatted as "general"

although i did'nt very well understand..what u meant by the last question?

i tried hyperlinking the sorted data, but it is hyperlinking the cells..which is causing a problem.. :(

do u have any other way.. in which we can search according to numerous criterias..in an excel file ?