Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with multiple sheets (2003 SP2)

    Good morning

    I have created a WB that has about 120 tabs, as each of them incoperates most of the same design features I have used the 'right clcik' on the tabs 'Select All Sheets' to input the same information.

    On all of the sheets I want to hyperlink a cell, G10, to cell A520 but using the method above does not work as the 'Hyperlink' option is greyed out when I right click with all tabs selected. Using the method of copying the cell and then highlighting all of the other tabs manually works but naturally pastes the link on each page back to sheet 1.

    Any ideas please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with multiple sheets (2003 SP2)

    You could run this macro:
    <code>
    Sub CreateHyperlinks()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    wsh.Range("G10").Hyperlinks.Delete
    wsh.Hyperlinks.Add _
    Anchor:=wsh.Range("G10"), _
    Address:="", _
    SubAddress:="'" & wsh.Name & "'!A520", _
    TextToDisplay:="Click Here"
    Next wsh
    End Sub
    </code>
    The macro can be discarded once you're satisfied with the result.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with multiple sheets (2003 SP2)

    Hi Hans

    Thanks as susual for the response. I have copied and pasted the macro but when run it returns the a debug error 28 after about 30 minutes and this is what I get when I say Yes to debug

    Sub CreateHyperlinks()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    wsh.Range("G10").Hyperlinks.Delete
    wsh.Hyperlinks.Add _
    Anchor:=wsh.Range("G10"), _
    Address:="", _
    SubAddress:="'" & wsh.Name & "'!A520", _
    TextToDisplay:="Click For Export Cargo"
    Next wsh

    Range("C5").Select
    Application.Run "ExportServiceGuide.xls!CreateHyperlinks"
    Range("G10").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveWindow.LargeScroll Down:=-8
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Yemen - YE").Select
    Range("G10").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Range("A498").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Application.WindowState = xlMinimized
    Application.WindowState = xlNormal
    Application.WindowState = xlNormal
    Application.WindowState = xlMaximized
    Range("H10").Select
    Sheets(Array("Index", "Afghanistan-AF", "Albania - AL", "Algeria - AZ", _
    "Andorra - AD", "Angola - AO", "Anguilla - AI", "Antigua & Barbuda - AG", _
    "Argentina - AR", "Armenia - AM", "Aruba - AW", "Australia - AU", "Austria - AT", _
    "Azerbaijan - AZ", "Bahamas - BS", "Bahrain - BH", "Bangladesh - BD", _
    "Barbados - BB", "Belarus - BY", "Belgium - BE", "Belize - BE", "Benin - BJ", _
    "Bermuda - BM", "Bhutan - BT", "Bolivia - BO")).Select
    Sheets("Afghanistan-AF").Activate
    Sheets(Array("Bosnia & Herzegovina - BA", "Botswana - BW", "Brazil - BR", _
    "Brunei - BN", "Bulgaria - BG", "Burkina Faso - BF", "Burma", "Burundi - BI", _
    "Cambodia - KH", "Cameroon - CM", "Canada - CA", "Cape Verde - CV", _
    "Cayman Islands - KY", "Central African Republic - CF", "Chad - TD", "Chile - CL", _
    "China - CN", "Christmas Islands - CX", "Cocos (Keeling) Islands - CC", _
    "Colombia - CO", "Comoros - KM", "Congo - CG", "Congo (DemocraticRepublic) -CD ", _
    "Cook Islands - OK", "Costa Rica - CR")).Select Replace:=False
    Sheets(Array("Cote D'Ivoire - CI", "Croatia - HR", "Cuba - CU", "Cyprus - CY", _
    "Czech Republic - CZ", "Denmark - DK", "Djibouti - DJ", "Dominica - DM", _
    "Dominican Republic - DO", "Ecuador - EC", "Egypt - EG", "El Salvador - SV", _
    "Equatorial Guinea - GQ", "Eritrea - ER", "Estonai - EE", "Ethiopia - ET", _
    "Falkland Island - FK", "Faroe Islands - FO", "Fiji - FJ", "Finland - FI", _
    "France - FR", "French Guiana - GF", "French Polynesia - PF", "Gabon - GA", _
    "Gambia - GM")).Select Replace:=False
    Sheets(Array("Georgia - GE", "Germany - DE", "Ghana - GH", "Gibralter - GI", _
    "Greece - GR", "Grenada - GD", "Guadeloupe - GP", "Guam - GU", "Guetemala - GT", _
    "Guernsey - GG", "Guinea - GN", "Guinea Bissau - GW", "Guyana - GY", "Haiti - HT", _
    "Heard & McDonald Islands - HM", "Honduras - HN", "Hong Kong - HK", "Hungary - HU" _
    , "Iceland - IS", "India - IN", "Indonesia - ID", "Iran - IR", "Iraq - IQ", _
    "Ireland - IE", "Israel - IL")).Select Replace:=False
    Sheets(Array("Italy - IT", "Jamaica - JM", "Japan - JP", "Jersey - JE", _
    "Jordan - JO", "Kazakhstan - KZ", "Kenya - KE", "Kiribati - KI", _
    "Korea (North) - KP", "Korea (South) - KR", "Kosovo", "Kuwait - KW", _
    "Kyrgyzstan - KG", "Lao - LA", "Latvia - LV", "Lebanon - LB", "Lesotho - LS", _
    "Liberia - LR", "Libya - LY", "Liechtenstein - LI", "Lithuania - LT", _
    "Luxembourg - LU", "Macao - MO", "Macedonia - MK", "Madagascar - MG")).Select _
    Replace:=False
    Sheets(Array("Malawi - MW", "Malaysia - MY", "Maldives - MV", "Mali - ML", _
    "Malta - MT", "Marshall Islands - MH", "Martinique - MQ", "Mauritania - MR", _
    "Mauritius - MU", "Mayotte - YT", "Mexico - MX", "Micronesia - FM", "Moldova - MD", _
    "Monaco - MC", "Mongolia - MN", "Montenegro - ME", "Montserrat - MS", _
    "Morocco - MA", "Mozambique - MZ", "Myanmar - MM", "Namibia - NA", "Nauru - NR", _
    "Nepal - NP", "Netherlands - NL", "Netherlands Antilles - AN")).Select Replace _
    :=False
    Sheets(Array("New Caledonia - NC", "New Zealand - NZ", "Nicaragua - NI", _
    "Niger - NE", "Nigeria - NG", "Niue - NU", "Norfolk Island - NF", _
    "Northern Mariana Islands - MP", "Norway - NO", "Oman - OM", "Pakistan - PK", _
    "Palau - PW", "Palestine - PS", "Panama - PA", "Papua New Guinea - PG", _
    "Paraguay - PY", "Peru - PE", "Philippines - PH", "Pitcairn - PN", "Poland - PL", _
    "Portugal - PT", "Puerto Rico - PR", "Qatar - QA", "Reunion - RE", "Romania - RO")) _
    .Select Replace:=False
    Sheets(Array("Russia - RU", "Rwanda - RW", "St. Barthelemy - BL", _
    "St. Helena - SH", "St. Kitts & Nevis - KN", "St. Lucia - LC", "St. Martin - MF", _
    "St. Pierre & Miquelon - PM", "St. Vincent&The Grenadines - VC", "Samoa - WS", _
    "San marino - SM", "SaoTome & Principe - ST", "Saudi Arabia - SA", "Senegal - SN", _
    "Serbia - RS", "Seychelles - sC", "Sierra Leone - SL", "Singapore - SG", _
    "Slovakia - SK", "Slovenia - SI", "Solomon Islands - SB", "Somalia - SO", _
    "South Africa - ZA", "Sth. Georgia & S. Sandwich - GS", "Spain - ES")).Select _
    Replace:=False
    Sheets(Array("Sri Lanka - LK", "Sudan - SD", "Suriname - SR", _
    "Svalbard & Jan mayen - SJ", "Swaziland - SZ", "Sweden - SE", "Switzerland - CH", _
    "Syria - SY", "Taiwan - TW", "Tajikistan - TJ", "Tanzinia - TZ", "Thailand - TH", _
    "Timor-Leste - TL", "Togo - TG", "Tokelau - TK", "Tonga - TO", _
    "Trinidad & Tobago - TT", "Tunisia - TN", "Turkey - TR", "Turkmenistan - TM", _
    "Turks & Caicos Islands - TC", "Tuvalu - TV", "Uganda - UG", "Ukraine - UA", _
    "United Arab Emirates - AE")).Select Replace:=False
    Sheets(Array("United Kingdom - GB", "United States - US", "Uruguay - UY", _
    "Uzbekistan - UZ", "Vanuatu - VU", "Venezuala -VE", "Vietnam - VN", _
    "Virgin Islands (British) - VG", "Virgin Islands (US) - VI", _
    "Wallis & Futuna - WF", "Western Sahara - EH", "Yemen - YE", "Zambia - ZM", _
    "Zimbabwe - ZW", "General Information")).Select Replace:=False
    Range("G10").Select
    Selection.ClearContents
    Range("B29:G29").Select
    Sheets("Afghanistan-AF").Select
    Range("G10").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Index").Select
    Range("G10").Select
    Sheets("Afghanistan-AF").Select
    Range("B29:G29").Select
    Sheets("Angola - AO").Select
    Range("G10").Select
    Sheets("Index").Select
    Range("G10").Select
    End Sub

    and all sheets hyperlink to sheet 2 'Afghanistan'

    Any suggestions please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with multiple sheets (2003 SP2)

    I sincerely hope that you didn't really expect me to read that code. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Working with multiple sheets (2003 SP2)

    I would guess that this line is your problem:
    <code>Application.Run "ExportServiceGuide.xls!CreateHyperlinks"</code>
    if your code is in the ExportServiceGuide workbook then you are calling the routine from itself so you end up in a loop.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with multiple sheets (2003 SP2)

    Hi Hans

    Sorry I thought I would post it to illustrate the result of the code that you gave me, I did not write it, it writes itself when I run the macro.

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with multiple sheets (2003 SP2)

    Hi Steve

    Thanks for the suggestion, that line seemed to write itself when I ran the macro on my laptop, however on my work machine it does not but it hyperlinks everything back to A520 on sheet 2 and not onto each individual sheet (there were 240 not 140 as originaly posted (typo)). I am now in the process of doing it the old fashioned way!!

    Thanks to you and Hans for input

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Off Course the code works!

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    If you have ever watched Dad's Army you will be familiar with the saying "Stupid boy Pike", I have to admit to having a 'Pike' moment

    I created a macro name for the hyperlink and then copied and pasted Han's code, in its entirity, into this which sent the code (which of course always worked) into terminal meltdown.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Working with multiple sheets (2003 SP2)

    <hr>Hi Steve
    ...
    Steve
    <hr>
    Yeah, I often find talking things through with myself helps to clarify them! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with multiple sheets (2003 SP2)

    Hi Hans

    Is it possible to exclude 4 specific sheets from this piece of code

    Index
    ExpressImports
    ExpressExports
    Currencies

    TIA

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with multiple sheets (2003 SP2)

    Yes, like this:
    <code>
    Sub CreateHyperlinks()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "Index", "ExpressImports", "ExpressExports", "Currencies"
    ' Do nothing
    Case Else
    wsh.Range("G10").Hyperlinks.Delete
    wsh.Hyperlinks.Add _
    Anchor:=wsh.Range("G10"), _
    Address:="", _
    SubAddress:="'" & wsh.Name & "'!A520", _
    TextToDisplay:="Click Here"
    End Select
    Next wsh
    End Sub
    </code>
    The additional lines use Select Case to handle the four specific sheets separately (in this case by doing nothing with them).

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with multiple sheets (2003 SP2)

    Hi Hans

    Thanks for the quick response, this bit of code has already saved me several hours of work and the amendment will save many more

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •