• 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

    Hui...

  • 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

uknwmedontu

New Member
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
 

uknwmedontu

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
 

Hui

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?
 

uknwmedontu

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 ?
 

Hui

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

[pre]
Code:
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

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 ?
 
Top