Hyperlinks with Advanced Filters

[sammyp1]

Hello,

I'm trying to copy-paste extracted data (3 columns - Columns B, C, D) from a 11 column dynamic range using advanced filters onto another worksheet. One of the extracted columns has hyperlinks and unfortunately does not successfully transfer over to the other worksheet. (Note - to be specific, the extracted data shows the underline but its not operable.) Here is the code I currently have. Could someone kindly let me know how I can resolve this:

Dim Crit As Range
Set Crit = Sheet3.Range("Criteria")

If Not Crit Is Nothing Then
    Sheet2.Range("MyTable").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Crit, _
    CopyToRange:=Range("Extract"), Unique:=False
End If

Thanks.

 

[Smallman]

Hi Sammyp1

The advance filter will not copy the data to another place and have the hyperlinks follow but a regular filter will. Put a sample file together with dummy data if need be and it will be easier to put something together for you. As a general rule - best to post the file with the code - makes it easier for all.

Take care

Smallman

 

[sammyp1]

Smallman,

My apologies. Attached is sample file (with some slight adjustments) found from the web that can used for this exercise. Assume the Main Actor column to be one with hyperlinks. Note that in the file, data can be extracted by either the drop down feature for the Category column or type in an actor name.

Hope this helps.

Thank you.

 

[Smallman]

SammyP1

It was not an ardious task to add the hyperlinks so I added a few for you. The code to run the file is as follows and I will attach the file to show workings.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cat As String
Dim Act As String
 
    If Not Intersect(Target, Range("C3, C5")) Is Nothing Then
        Application.EnableEvents = False
        [B9:G100].Clear
        Cat = IIf([C3] = "", "*", [C3].Value)
        Act = IIf([C5] = "", "*", [C5].Value)
        wksMovies.[C1:G100].AutoFilter 1, Cat
        wksMovies.[C1:G100].AutoFilter 4, Act
        wksMovies.Range("B2", wksMovies.Range("G" & Rows.Count).End(xlUp)).Copy [B9]
        wksMovies.[c1].AutoFilter
        Application.EnableEvents = True
    End If
 
 
End Sub

Take care

Smallman

 

[sammyp1]

Thank you Smallman. I will look to test this procedure with my current work and get back to you with my results. Again, do appreciate your assistance.

 

[Smallman]

No worries Sammyp1

Just come back if you have any further questions. The file in its current form seems to go OK.

Take care

Smallman

 

[sammyp1]

Smallman, it appears that the initial code for my sample file under the SelectMovies tab was completely replaced with your code. The problem here is that I would like to use that sample file's features in cell C3 and C5 as well as the use of copying selected data from the master database (found on the MoviesList tab) to the SelectMovies tab with advancedfilters. This prevents the user from adding or deleting data to the master database. In short, I'd very much like to keep the original code found on the file and simply add to it the capability of extracting hyperlinks from the database to the SelectMovies tab.

Apologies if I was not clear with this from the start.

Thank you.

 

[Smallman]

@Sammp1

The file I provided can be viewed as a sample. You didn't post your real file anyway as there was no hyperlinks in the file at all. It is your job to take the code I have given you and incorporate it into your real workbook (the one you did not post).

If you are unable to do so post your real workbook the one with the hyperlinks (see thread title) and ask a specific question as I am having trouble deciphering what the question is from post 6.

Take care

Smallman

 

[sammyp1]

Smallman, I do apologize for making this difficult. Let take a stab at providing a skeleton version of my work, and provide specifics. It will take some time.

Again, appreciate your time and effort in assisting me.

 

[Smallman]

No worries. Happy to have a look at your file once you get it together.

Take care

Smallman

 

[sammyp1]

Smallman,

Here is the draft version of my actual workbook. Everything is complete except for the hyperlink issue (as you can under the USERFORM tab) and the insertion of the actual data (in sections highlighted in yellow and some 57 questions and responses in the database.

Based on this file, and the drop down and search features provided, could the hyperlinks be operable on the USERFORM tab when extracted?

Again, any assistance in the code would be greatly appreciated.

 

[Smallman]

Sammyp1

From your post;

Based on this file, and the drop down and search features provided, could the hyperlinks be operable on the USERFORM tab when extracted?

No, not using your method. As I said above your code with the Advanced Filter won't work with hyperlinks.

However I can adapt the code I provided before to do what your code (with the Advanced Filter) does with the added advantage of providing hyperlinks on the Userform page. If you are attached to your coding I appologise I can not help you.

If you are open to change and a more slimline version of the code you are using then yes I can help you.

Take care

Smallman

 

[sammyp1]

Thank you for your time Smallman, but I think I'll stick with what I have and leave it as is.

 

[Debraj]

Hi Sammyp..

Try this..

If Not rngCrit Is Nothing Then
    Sheet2.Range("RFPTable").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=rngCrit
    'CopyToRange:=Range("ExtractRFP"), Unique:=False

    Sheet1.Range("B8").CurrentRegion.Clear
    On Error Resume Next
    With Sheet2.Range("RFPTable").Range("B1:D58") 'You cna set this range dynamic
        .SpecialCells(12).Copy Sheet1.Range("B8")
    End With
    On Error GoTo 0
    Sheet2.Range("RFPTable").AutoFilter
End If

just a small bridge between Your shortcut requirement & Marcus's perfect Coding..

 

[sammyp1]

This works perfectly. Thank you Debraj!!!!!

Just for my understanding, why use 'On Error Resume...On Error GOTO" ?

 

[Debraj]

Actually not required in this case...
Its just my habit to put "Error Handling" in case of "No Data Found in Filter or Special Cells"