ahortened VBA code

[highndry]

Hi

I have the following code and I was wondering if I could write a single select statement to choose the multiple sheets and single statement to remove the hyperlink.

Sheets("Associate").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Hyperlinks.delete

Sheets("OpenReqs").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Hyperlinks.delete

Sheets("Contractor").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Hyperlinks.delete

Thanks

 

[Smitty]

Something like this maybe:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> RemoveHyperLinks()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            ws.Range("A1").Hyperlinks.Delete
        <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[highndry]

Hi Pennysaver

Thanks for a quick response, would your code delete the hyperlink in other tabs? I only want the hyper link deleted in "Associate", "OpenReqs", and "Contractor" tabs.

Thanks

 

[Smitty]

How's this:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> RemoveHyperLinks()
    <SPAN style="color:#00007F">Dim</SPAN> CurSheet <SPAN style="color:#00007F">As</SPAN> Worksheet
        <SPAN style="color:#00007F">Set</SPAN> CurSheet = ActiveSheet
        
        Sheets(Array("Associates", "OpenRegs", "Contractor")).Select
        Range("A1").Hyperlinks.Delete
        CurSheet.Activate
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

 

[highndry]

Hi Pennysaver

This code only removes the hyperlink from the Associate tab and not the others.

Thanks

 

[Barrie Davidson]

Not tested, but what about:

Sheets("Associate").Range("A1").Hyperlinks.delete 
Sheets("OpenReqs").Range("A1").Hyperlinks.delete 
Sheets("Contractor").Range("A1").Hyperlinks.delete

 

[highndry]

Thanks Barrie Davidson that worked.

 

[Smitty]

Sorry, that's what I get for not testing (or using hyperlinks much).

Smitty

 

[Barrie Davidson]

Sorry, that's what I get for not testing (or using hyperlinks much).

Smitty

A "gust of gravity" moment! I get 'em all the time. :wink: