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

[uknwmedontu]

Hi

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


exitHandler:

Application.EnableEvents = True

Exit Sub

errHandler:

Resume exitHandler


End Sub


Please reply back soon.

Thanks

 

[Hui]

Uknwmedontu

Which range has the hyperlinks ?

 

[uknwmedontu]

E2:E65 in the orignal data has hyperlinks

 

[uknwmedontu]

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

 

[uknwmedontu]

Is there a better and a more beautiful way to implement the above search criteria ?

 

[Hui]

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?

 

[uknwmedontu]

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 ?

 

[Hui]

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

[pre]

wksResRep.Range("B1:W65").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=rngCrit, _
CopyToRange:=Range("ExtractDetails"), Unique:=False

[/pre]
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.

 

[uknwmedontu]

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 ?

 

[Hui]

I'd do the last bit with the Advanced Filter manually

 

[uknwmedontu]

Please do let me know..how you can help me in it.

Thanks a ton