Worksheets in VBA Coding and in Worksheet Formulas

Some miscellaneous SHEET coding.  been transferred from my pathname, lastcell and buildtoc pages.

Background information on Sheets   (#intro)

Review of Worksheet Formulas

Another way of creating your worksheet formula is to start by placing an equal sign on the formula bar to start a formula, then select a cell on the other sheet which could be in another open workbook.  The formula is created for you.  Return to your original sheet and hit Enter.

characters not allowed in sheetnames  : \ ? * [ ]
Sheetnames may be up to 31 characters in length.

Sheetnames in macros

  For csht = 1 To ActiveWorkbook.Sheets.Count  'worksheet or sheets
     Cells(cRow - 1 + csht, cCol) = "'" & Sheets(csht).Name
     Cells(cRow - 1 + csht, cCol + 1) = Sheets(Sheets(csht).Name).Range("A1").Value
  Next csht

  Sub msgthis(sht As String)
    MsgBox sht & " -- " _
       & Sheets(sht).Range("A1").Text
  End Sub

  Sub MsgBoxAllMySheets2()
    Dim sht As Worksheet
    For Each sht In Sheets
      msgthis (sht.Name)
    Next sht
  End Sub

  Sub MsgBoxAllMySheets()
    Dim sht As Worksheet
    For Each sht In Sheets
      MsgBox sht.name
    Next sht
  End Sub

  Sub AllSheetsColorFormulas()
    Dim sht As Worksheet
    For Each sht In Sheets
      On Error Resume Next 'in case no formulas
      sht.Cells.SpecialCells(xlFormulas). _
            Interior.ColorIndex = 6
          Next sht
  End Sub

   Sub ARRAY_sheetnames()
   Dim wksht As Worksheet
   Dim i As Long
         Dim wkshtnames()     'This is an array definition
    i = 0
    For Each wksht In ActiveWorkbook.Worksheets
        i = i + 1
        ReDim Preserve wkshtnames(1 To i)
        wkshtnames(i) = wksht.Name
    Next wksht

    For i = LBound(wkshtnames) To UBound(wkshtnames)
        MsgBox wkshtnames(i)
    Next i
    End Sub

Dim cell As Range
Dim sh as Worksheet
for each sh in Worksheets(Array("X340", "X342n","X642e"))
For Each cell In sh.Range("T4:T53")
    If len(trim(cell.Text)) = 0 Then
    MsgBox "There is data missing" & cell.Address
    Application.Goto cell, True
    Cancel = True
    Exit For
    End If
Next cell
Next Sh

Sub SelectSheetsBasedOn_B()
  Dim rng As Range, cell As Range
  Dim arrNames() As String, I As Long
  On Error Resume Next
  Set rng = Range("B:B").SpecialCells(xlConstants, xlNumbers)
  If Err.Number <> 0 Then
    MsgBox "Error " & Err.Number & " -- " & Err.Description
    Exit Sub
  End If
  On Error GoTo 0
  If Not rng Is Nothing Then
      'dimension to max possible names in array
      ReDim arrNames(1 To rng.Count)
      For Each cell In rng
        If cell.Value = 1 Then
           I = I + 1
           arrNames(I) = cell.Offset(0, -1).Value
        End If
      Next cell
  End If
  'reduce to names to be used
  ReDim Preserve arrNames(1 To I)
  Sheets(arrNames).Select
End Sub

• Use of SpecialCells in Proper Case macros (proper.htm)
• Running a macro for all worksheets in a workbook (buildtoc.htm)
• Arrays (arrays.htm)
• Worksheets in VBA Coding and in Worksheet Formulas (this page -- sheets.htm)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newSht As String, oldSht  As String
    Dim wsOld As Worksheet, wsNew As Worksheet
    If Target.Column <> 6 Or Target.Row = 1 Then Exit Sub
    oldSht = ActiveSheet.Name
    Set wsNew = ActiveSheet
    newSht = Target.Text
    On Error Resume Next
    Sheets(newSht).Activate
    If Err.Number = 0 Then    'sheetname already exists
       Sheets(oldSht).Activate
       Exit Sub
    End If
    On Error Resume Next
  'Create New Sheet
    Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
    ActiveSheet.Name = newSht
    Set wsNew = ActiveSheet
    wsNew.Cells(1, 1) = "'" & newSht  'name of new sheet into cell
  '  Sheets(Sheets.Count).Activate  'try to show last tab
    Sheets(oldSht).Activate
End Sub

  Option Explicit
  Sub WsReplaceLooseCommas()
     Dim ws As Worksheet
      For Each ws In ActiveWorkbook.Worksheets
      ws.Cells.SpecialCells(xlCellTypeConstants, 2). _
         Replace What:=",", Replacement:=" ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False
      Next ws
  End Sub

      Sheets("##33##").Name = "## 33 $$'"""

      mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
      MsgBox "Macro result: " & mySum

      ActiveSheet.Name = "01343"
      ActiveSheet.Name = Format(123, "0000")
      ActiveSheet.Name = "04-03-2001"                 ' NOT recommended doesn't sort properly
      ActiveSheet.Name = Format(Date, "mm-dd-yyyy")   ' NOT recommended doesn't sort properly
      ActiveSheet.Name = "2001-04-03"   
      ActiveSheet.Name = Format(Date, "yyyy-mm-dd")
      ActiveSheet.Name = target.Text

     Private Sub Worksheet_Change(ByVal Target As Range)
         If Target.Address = "$A$1" Then
          On Error Resume Next
          ActiveSheet.Name = Format(Range("A1"), "yyyy-mm-dd")
          If Err.Number <> 0 Then
            MsgBox Err.Number & " " & Err.Description
            Err.Clear
          End If
         End If
         On Error GoTo 0
      End Sub

Some Sheet related coding   (#sheetcoding)

Sub Macro29()
'Every macro should have this of course...
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    '    ooo   Your code here   ooo 
    Application.Calculation = xlCalculationAutomatic 
    Application.ScreenUpdating = True 

'active worksheet name, address and index
    MsgBox ActiveSheet.Name & "  " & ActiveCell.Address(0, 0) & 
       "   sheet(" & Application.Caller.Parent.Index  & ")"
'Create a New Workbook containing only one sheet
                Workbooks.Add xlWorksheet
                set newwb = workbooks.add(1)
                set newwb = Workbooks.Add(xlWBATWorksheet)

'Create New Sheet
    Sheets.Add 
    Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
    Sheets.Add Before:=ActiveSheet          '-- default
    Sheets.Add After:=ActiveSheet
'Rename current Sheet
    ActiveSheet.Name = "Renamed14a"
    ActiveSheet.Name = "D" & Format(Date, "yyyymmdd")
    ActiveSheet.Name = "D" & Format(Range("a1"), "yyyymmdd")
    ActiveSheet.Name = "D" & Format(Range("a1"), "yyyy_mmdd_hhmm")
'Create New Sheet and name it NewSheet
    activeworkbook.Worksheets.Add(After:=Activesheet).Name = "NewSheet"
'Delete a sheet
    Application.DisplayAlerts = False
    '--Sheets("testing-copy").Delete
    Activesheet.Delete 
    '--ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
'Add sheet from a Template Library
    Sheets.Add Type:=Application.TemplatesPath & "\xxxxx.xlt"  '2004-05-10 Ron de Bruin 
'Activate a Sheet (what is difference activate/select)
    Sheets("Sheet14").Activate
'Select a Sheet, actually may be current sheet
    Sheets("Sheet14").Select
'Rename a Sheet, actually may be current sheet
    Sheets("Sheet14").Name = "Renamed14"
'Select an Existing Sheet
    Sheets("Map").Select
'Obtain sheetname and codename of current sheet
    msgbox activesheet.name & " codename is " & activesheet.codename
'Copy another sheet similar to using a template 
    '--manually:  Edit,  Move or Copy Sheet, be sure to check the copy)
    'copy after last tab of current workbook
    Worksheets("MyBeginning").Copy after:=Worksheets(Worksheets.Count) 
    'copy sheet from another workbook before first tab of Book2
     Workbooks("Book1").Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1) 
    ActiveSheet.Name = newName
'Copy the active worksheet to just before the currently active sheet [copy sheet]
    ActiveSheet.Copy Before:=ActiveSheet
'Copy the active worksheet to after the last worksheet 
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
   '-- special coding is required if some cells have more than 255 characters 
'Copy the first sheet
     Sheets(1).Copy After:=Sheets(1)    'new sheet might look like  first_sheet (2)
'Copy the last sheet
    Sheets(ActiveWorkbook.Sheets.Count).Select
    ActiveSheet.Copy after:=ActiveSheet
   '-- special coding is required if some cells have more than 255 characters

'Current position of sheet in the sheet tabs
    SheetIndex = Application.Caller.Parent.Index  '- in a function
    SheetIndex = ....construct needed for active sheet... 
'Move a specific worksheet to the front
    Sheets("Data").Move Before:=Sheets(1)  
'Move a specific worksheet to the end
    Sheets("Data").Move After:=Sheets(sheets.count)
'Select all cells on a worksheet
    cells.Select
'Select all cells on a worksheet within the used range
    ActiveSheet.UsedRange.Select
'Process all cells on a workshet within used range with text constants
    For Each cell In Cells.SpecialCells(xlConstants, xlTextValues)
         cell.Value = CDbl(cell.Value)
    Next cell
'Select an area within a worksheet
    set rng = Application.Inputbox("Select range",Type:=8)
'Select an area within a macro, with current selection as default
    Set Rng = InputBox("Select range", "TITLE1", Selection.Address(0, 0))
'Select an area within a worksheet, default current selection, redo with cursor
    Dim Rng As Range
    Set Rng = Application.InputBox("Select range", "TITLE1", _
       Selection.Address(0, 0), Type:=8)
    MsgBox Rng.Address(0, 0)
    Rng.select
    'It is seldom necessary to change the selection within a macro
    'the selection would be changed because you to see it, but the
    'macro could work with Range(Rng) just as easily as selection.range

'Recreate "testing-copy" Worksheet from "testing-skel" worksheet
    Sub testing_skel_copy()
      On Error Resume Next
      Dim svAlerts As Boolean
      svAlerts = Application.DisplayAlerts
      Application.DisplayAlerts = False
      Sheets("testing-copy").Delete
      Application.DisplayAlerts = svAlerts
      Sheets("testing-Skel").Copy Before:=Sheets("testing-skel")
      If LCase(Left(ActiveSheet.Name, Len("testing-skel"))) _
         = "testing-skel" Then ActiveSheet.Name = "testing-copy"
    End Sub

Some CELL related coding and Row / Column related as well   (#cellcoding)

'Assign a range of cells
    Range("A1:D1") = Array("Sheet Name", "A1", "B1", "C1", "textbox1")
    Rows("1:1").Font.Bold = True
    Cells(i, 1) = wkSheet.Name       -- where wkSheet is set for each sheet
    Cells(i, 2) = wkSheet.Cells(1, 1).Text
 
'Clear constants past row 1 and past column 1; leaving intact formulas, and formatting.
    On Error Resume Next
    Range("B2:" & Cells.SpecialCells(xlLastCell).Address). _
       SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
    Range("B2:" & Cells.SpecialCells(xlLastCell).Address). _
       SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

'Clear constants in a selection  (also see Insert Rows maintain formulas)
    Selection.SpecialCells(xlConstants).ClearContents
'Delete entire row
    CELLS(R,1).EntireRow.delete
    Rows(R).delete
'Delete entire column
    CELLS(1,C).EntireColumn.delete
    Columns(C).delete
'Fix lastcell after doing deletions (not a sure thing, see resetall)
    r = ActiveSheet.UsedRange.Rows.Count 'attempt fix lastcell aft deletes 
'Find firstcell in a column (see  personal.xls!GotoTopOfCurrentColumn )
    Cells(1, ActiveCell.Column).Select      
'Find lastcell in a column   (see  personal.xls!GotoBottomofCurrentColumn)
    Dim lastrow as long
    lastrow = activesheet.Cells(Rows.Count, "A").End(xlUp).Row
'Get the last row column of the used range
    lastrow = Cells.SpecialCells(xlLastCell).Row
'Top left cell of a range
    MsgBox UsedRange.Address(0,0)  '(row,column)  1=absolute
'Bottom right cell of a range
    msgbox usedrange(1)(1).offset(usedrange.rows.count-1,usedrange.columns.count-1).address(0,0)

Number of pages to be printed   (#pagenumbers)

Thought I'd include this specifically from a posting by Eric Desart since there was some question as to coding. (all quotes in these formulas are double quotes)

NumPages1 = ExecuteExcel4Macro("Get.document(50,"")")
Does Not work = gives Error

NumPages2 = ExecuteExcel4Macro("Get.document(50)")
Returns number of pages to be printed of active sheet.  (So not a good formula to use, see note)

NumPages3 = ExecuteExcel4Macro("Get.document(50,""Sheet2"")")
Returns number of pages to be printed of Sheet 2 even if not active.  

NumPages3 = ExecuteExcel4Macro("Get.document(50,""" & mysheet & """)")
Returns number of pages to be printed of variable mysheet containing the sheetname. 

NumPages4 = ExecuteExcel4Macro("Get.document(50,""[TEST.XLS]Sheet3"")")
Returns number of pages to be printed of File TEST.XLS Sheet 3 even if
not active.

• Display Page x of y in a cell, by Jan Karel Pieterse, in his blog (Daily Dose of Excel), 2004-12-12, also see comments, and you can also get the row number of the page after a page break.
• Don't copy a cell with ExceteExcel4Macro to another cell, copy from the formula bar instead (may crash otherwise, Jan Karel Pieterse)
• Build Table of Contents, similar listings, working with Hyperlinks
• Code to count all print pages from all worksheets & from all open workbooks without activating that worksheets\workbooks.  Shailesh Shah, programming, 2002-10-30.  For Excel 2007 users Shailesh has an add-on, “Exshail Classic Menu for Excel-2007 to make a usable menu from the less than useless Excel 2007 Ribbon.

Check for Existence of a Sheet before creating   (#existence)

Function WorksheetExists(WSName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Len(Worksheets(WSName).Name) > 0
End Function

Then, in your code,

If WorksheetExists("Summary") = True Then
    MainMacro
Else
    CreateSummarySheet
End If

Check for Existence of a Sheet -- Worksheet Solution

Naming NEW sheets   (#newsheets)

Sub AddNewSheet()
   Dim xlSheet As Worksheet
   Set xlSheet = ActiveWorkbook.Sheets.Add
   xlSheet.Name = "My New Worksheet"
   Set xlSheet = Nothing
End Sub

Creating a series of sheets based on a master sheet (#master)

The following is a rather interesting example posted by Don Guillet (2005-08-28, programming) The macro is renaming the copied sheet with a name of "Master (2)" to "Master1", and then "Master2", etc.

Sub MasterCopies()
Dim i As Long
Application.ScreenUpdating = False
  For i = 1 To 10
    Sheets("Master").Copy After:=Sheets(Sheets.Count)
    Sheets("Master (2)").Name = "D" & Format(i,"000")
Next
Application.ScreenUpdating = True
End Sub

Cycling through sheets looking for specific value in cell A1 (#search)

Sub MatchWStoA1()
 '//  Find cell A1 that contains  Customer Name
 '//  David McRitchie   2000-07-15 programming for L.Wong
  Dim cn As String, cn2 As String, ws As Worksheet
  cn = InputBox("Enter customer name, as found in cell A1")
retry:
  If cn = "" Then Exit Sub
  cn = Trim(LCase(cn))
  Dim cn3 As String
  cn3 = cn
  For Each ws In ActiveWorkbook.Sheets
     cn2 = Trim(LCase(Sheets(ws.Name).Range("A1").Value))
     cn2 = Replace(cn2, Chr(160), "")
     If cn2 = cn Then         'HELP, Working with Active Cell
        Worksheets(ws.Name).Activate    'must do this first
        Worksheets(ws.Name).Range("A1").Activate
        Exit Sub
     End If
  Next ws
  cn = InputBox(cn & " -- Not Found" & Chr(10) & _
         "REENTER Customer name, or hit [Cancel]")
  GoTo retry
End Sub

I think this is going to drive people crazy why not rename the sheets with the customer name or at least build a table of contents with information. See my buildtoc.htm page all you need to do is add content of cell A1 from each sheet.  You will also find information there for sorting sheetnames.

Scroll for position in window of active cell   (#scroll)

Display Cell A1 in upper left corner of sheet

  Sub Macro35()
    'Select cell without data and display top left corner of sheet
    'Dim lastrow As Long
    'lastrow = Cells.SpecialCells(xlLastCell).Row
    'Range(Cells(lastrow + 1, 1), Cells(lastrow + 1, 1)).Select
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
  End Sub

Display Active cell in upper left corner of sheet

  Sub Macro35()
    ActiveWindow.ScrollColumn = ActiveCell.Column
    ' ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = ActiveCell.Row
  End Sub

Navigations within a sheet based on another sheet   (#navigation)

Note both A23 and A24 refer back to G23, so you cannot write a program directly reversible as where to go backwards in the macro from G23.

Sheet24_Nav
A23:  =Sheet24!G23
G23:  =Sheet24!A23
A24:  =Sheet24!G23



Sheet24
select  C23  and run macro, goes to next cell because
    no formula in sheet24_nav!C23
select  A23  and run macro,  goes to G23, because
    sheet24_nav!A23 contains formula  =Sheet24!G23

Sub NavigateToFormulaOn_Nav()
    Dim sFormula As String
    On Error GoTo notgood
    sFormula = Worksheets(ActiveSheet.Name & "_nav"). _
         Range(ActiveCell.Address).Formula
    If Left(sFormula & " ", 1) <> "=" Then GoTo notgood
    Range(Mid(sFormula, 2)).Activate
    Exit Sub
notgood:
    ActiveCell.Offset(0, 1).Activate
End Sub

Navigation to a Sheetname (#moresheets)

Sub SheetList_CP()
  'Chip Pearson, 2002-10-29, misc., %23ByZYZ3fCHA.1308%40tkmsftngp11 
  'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0%40msn.com
  On Error Resume Next
  Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
  If Err.Number > 0 Then
    Err.Clear
    Application.CommandBars("Workbook Tabs").ShowPopup
  End If
  On Error GoTo 0
End Sub

You can hold the shift key down click on the worksheet tab navigation arrows to shift over to the next set of worksheet tabs currently out of sight.

Some Loops through Worksheets   (#loopsheets)

The LoopThru which resembles your original will reach a limit on characters in the msgbox -- limit varies by version.

The LoopSheet will fail to show all sheets because due to limit or failure to be able to show another box after the first fills up the monitor window.

More useful examples can be found in
Build Table of Contents and optionally sort worksheets
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Option Explicit
Public Sub LoopThru()
Dim mostofthem As String
Dim sheet As Variant
For Each sheet In ActiveWorkbook.Sheets
    mostofthem = mostofthem & sheet.Name & ", "
Next sheet
MsgBox mostofthem
End Sub

Public Sub LoopSheets()
  Application.Calculation = xlManual   'xl97 up use xlCalculationManual
  Application.ScreenUpdating = False
  Dim mostofthem As String
  Dim csht As Long
  mostofthem = ""
  For csht = 1 To ActiveWorkbook.Sheets.Count  'worksheet or sheets
     mostofthem = mostofthem & Sheets(csht).Name & Chr(10)
  Next csht
  MsgBox mostofthem, , "names of sheets"
  Application.ScreenUpdating = True
  Application.Calculation = xlAutomatic   'xl97 up use xlCalculationAutomatic
End Sub

Public Sub LoopShorter()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        MsgBox ws.Name
        '   ws.Protect password:="AnyThing"
    Next ws
End Sub

 Sub Martin002()
     'place 'Martin 0'!A1 value into distinct sheets
     Dim x As Variant
     Dim sht As Variant
     x = Array("Martin 1", "Sheet2", "MSFT")
     Sheets("Martin 0").Activate
     For Each sht In x
       Sheets(sht).PageSetup.LeftHeader = ActiveSheet.Cells(1, 1)
     Next sht
 End Sub

Rename Sheet   (#rename)

 Sub RSHEET()
   'Rename Sheet,  D.McRitchie, 2001-04-05 programming
     Dim xStr As String
retry:
     Err.Clear
     xStr = InputBox("Supply new name for old sheet,  blah," _
       & "blah,blah", "Rename Sheet", ActiveSheet.Name)
     If xStr = "" Then Exit Sub
     On Error Resume Next
     ActiveSheet.Name = xStr
     If Err.Number <> 0 Then
        MsgBox Err.Number & " " & Err.Description
        Err.Clear
        GoTo retry
     End If
     On Error GoTo 0
     '... continue......
 End Sub

Delete Active Sheet if name begins with "sheet"   (#DeleteThisSheet)

An exception for sheets with program code -- they will not be deleted nor will you be asked if you want to delete them. 

I have buttons to go through the sheet tabs,     navigating to the Previous and Next described in BuildTOC, but you can use Ctrl+PageUP (to left) and Ctrl+PageDN (to right).  The Toolbar icon for this macro is included on the toolbars page.  Coding is with code/buildtoc.txt rather than code/sheets.txt

Sub DeleteThisSheet()
   Dim ans As Variant
   Dim saveTrue As Variant
   Dim sheetcodelines As Long
   '--requires ref. to the MS VBA Extensibility' library
   sheetcodelines = ActiveWorkbook.VBProject.VBComponents _
         (ActiveSheet.CodeName).CodeModule.CountOfLines
   If sheetcodelines > 2 Then
      MsgBox ActiveSheet.Name & " -- has " & _
         sheetcodelines & " lines of code (not deleted) "
   ElseIf Left(ActiveSheet.Name, 5) = "Sheet" Then
      Application.DisplayAlerts = False
      ActiveSheet.Delete
      'tflush_wav
      Application.DisplayAlerts = True
   Else
      Application.DisplayAlerts = True
      ActiveSheet.Delete
   End If
End Sub

   ElseIf Left(ActiveSheet.Name, 5) = "Sheet" then

   ElseIf Left(ActiveSheet.Name, 5) = "Sheet" _
         Or Application.WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then

Delete Empty Sheets (#delete_emptysheets)

Sub Delete_EmptySheets()
    Dim sh As Worksheet 'Ron de Bruin, programming, 2002-12-28 
    For Each sh In ThisWorkbook.Worksheets
      If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
        Application.DisplayAlerts = False
        sh.Delete
        Application.DisplayAlerts = True
      End If
    Next
End Sub

Linked files in Excel   (#linked)

 D:\myfolder\excel\TAXES\myfile.xls - checking!B600
 d:\myfolder\excel\taxes\myfile.xls[checking]!b600
 ="d:\myfolder\excel\taxes\" & A14 & "["& A15 & "]!"&"b600"
 =myfile.xls!getformula(B2)

VBA gotocell reference   (#gotocell)

 Application.Goto Reference:="sheetaa!C14"

VBA goto subroutine/macro/function   (#goto) ref.

Application.Goto Reference:=ActiveCell.Value

Application.Goto Reference:=Mid(ActiveCell.Formula, 2, i - 2)

Coding a link address and friendly name using HYPERLINK Worksheet function (#hyperlink)

Worksheet code that created a hyperlink

=HYPERLINK("[c:\temp folder\xyz abc.xls]'Sheet One'!$C$5","Sheet One")
     [http://www.mvps.org/dmcritchie/excel/excel.htm]","My Excel Pages")
=HYPERLINK("file:\\\c:\temp\David McRitchie\a.txt","thisone is good")
=HYPERLINK("mailto:DMcRitchie%20%6Dsn.com","David McRitchie")

It appears that even if the link is to a cell in the same worksheet you must include the bookname including .XLS extension, as well as the sheetname.
  =HYPERLINK("[WBName.xls]Michael!C5", "Michael I")

Until someone pointed out that you can use the pound sign (#)

By including the "#" the pathname can be left off, these work: in Excel 2000, and as I understand from Excel 97 and up, but these references are frozen within double quotes.  See the next topic to take the references out from within the quotes.
  =HYPERLINK("#C5",C5)
  =HYPERLINK("#Sheet34!E6",Sheet34!E6)
  =HYPERLINK("#'My Sheet'!E6",Sheet34!E9)

Hyperlink Worksheet References outside of quotes   (#hyperlink_ref)

Since the above links are within double quotes they will not adjust when you insert or delete rows or columns.  Double quotes also impairs several of the other examples on this page.  Even absolute addresses would change when inserting or deleting rows/columns but not things within quotes.  To take care of that use...

The HYPERLINK Worksheet Formula can be used to refer to a cell on the same worksheet.  The CELL Worksheet Function can be used to insure that you can refer to a relative address that survives insertion/deletion of rows/columns since it is not frozen within double quotes.

  =HYPERLINK("#"&CELL("address",C5),C5)
  =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5) 
  =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5) 

No address cell is within double quotes so the formulas will adjust if you insert/delete rows or want to use the fill-handle.

	A	B	C	D
1	D4	=HYPERLINK("#"&C3,C3)	D3	D1-value
2	D4-value	=HYPERLINK("#"&C3,INDIRECT(C3))	C2	D2-value
3	D4-value	=HYPERLINK("#"&CELL("address",D4),D4)	D4	D3-value
4	D3-value	=HYPERLINK("#D3",D3)	C4	D4-value

D4:   =HYPERLINK("#"&CELL("address",A3),"D4-value")

To hyperlink and display the Cell in the B column same row of the Notes sheet
  =HYPERLINK("#"&CELL("address",OFFSET(Notes!B1,ROW()-1,0)), OFFSET(Notes!B1,ROW()-1,0))

To hyperlink to a more descriptive longer note on the same row, but in the B column of the Notes worksheet.
  =HYPERLINK("#"&CELL("address",OFFSET(Notes!B1,ROW()-1,0)), "Click for note")

To hyperlink to the Note in Column B same row on Notes sheet, but display the short description in Column A of the Notes sheet.
  =HYPERLINK("#"&CELL("address",OFFSET(Notes!B1,ROW()-1,0)), OFFSET(Notes!A1,ROW()-1,0))

To refer to a cell in Column D (col 4) of the same sheet at a specific Row OFFSET to the row
  =HYPERLINK("#" & ADDRESS(ROW()+41,1,4),"next week")
takes you down 41 rows to the first column, so the formula can be in any cell on the row.  The 4 indicates a relative address result, but it really doesn't matter if absolute or relative since it is created during recalculation. Will allow you to put the formula on any cell in the row , the link will take you 41 rows below and to the first column.

Some alternatives to Hyperlinks within a worksheet or within a workbook

• An Event macro such as double-click that asks for the first letter(s) of a lastname and then invokes the Match Worksheet Function to get you there.  The event macro example is used with a name and address list sorted by lastname.
• An example of a Double-click (or right-click) event macro to goto another worksheet with the FollowHyperlink Method and specific cell using ActiveWorkbook.FollowHyperlink, there is perhaps a better example on this page that goes to another sheet with HELP information and provides the means of returning with the BACK button.

CELL is a Volatile function, but worksheet volatile functions do not present the serious slowdowns that Volatile User Defined (VBA) Functions can have.

xlindex.htm is a sheet on my page, on my HD and on my website xlindex is also a sheet in my vlookup.xls file the following examples show Column B what you see, Col C is the formula, and "C5-1" is the value in cell c5 on sheet xlindex

	A	B	C
1	xlindex	'xlindex'!C5	=HYPERLINK( "#" & A1 & "!C5","'" & A1 & "'!C5")
2	xlindex	xlindex!C5	=HYPERLINK("#'" & A2 & "'!c5",A2 & "!C5")
3	xlindex	xlindex!C5	=HYPERLINK("#xlindex!c5",A3 & "!C5")
4	xlindex	C5-1	=HYPERLINK("#xlindex!c5",xlindex!C5)
5	xlindex	'xlindex'!C5	=HYPERLINK("[vlookup.xls]'" & A5 & "'!C5","'" & A5 & "'!C5")
6	xlindex	xlindex	=HYPERLINK("file://x:/mywebsite/dmcritchie/excel/" & A6 & ".htm",A6)
7	xlindex	xlindex	=HYPERLINK("http://www.mvps.org/dmcritchie/excel/" & A7 & ".htm",A7)
8	(same sheet)	C5-1	=HYPERLINK("#"&CELL("address",C5),C5)

Values in RED are shown as constants if used in an actual formula referring to cells in the same workbook the formula is reduced to the formula shown immediately below -- no changes occur within double-quotes.

	A	B
1	Value	=PERSONAL.XLS!Getformula(cell)
2		'-- using Excel 2000 9.09.6926 SP-3
3	A18-Value	='Menu Sheet'!A18
4	(--->)	'='C:\temp\[project_text.xls]Menu Sheet'!A19
5	A18-Value	='Menu Sheet'!A18
6	A18-Value	=HYPERLINK("[project_text.xls]'Menu Sheet'!a18", 'Menu Sheet'!A18)
7	A18-Value	=HYPERLINK("[c:\temp\project_text.xls]'Menu Sheet'!a18", 'Menu Sheet'!A18)
8	A18-Value	=HYPERLINK("c:\temp\project_text.xls#'Menu Sheet'!a18", 'Menu Sheet'!A18)
9	(--->)	'=HYPERLINK("[c:\temp\project_text.xls]'Menu Sheet'!a18", 'C:\temp\[project_text.xls]Menu Sheet'!A18)
10	A18-Value	=HYPERLINK("[c:\temp\project_text.xls]'Menu Sheet'!a18", 'Menu Sheet'!A18)

Hyperlink to a MATCH (#hyperlinkmatch)

Hyperlink to a value in a table.

=HYPERLINK("#"&ADDRESS(MATCH("xyz",B1:B13),2), "xyz" )

You are looking for the value "xyz" (not case sensitive)  in B1:B13  

if you don't want to see #N/A then insert the failing part of the formula and the formula as follows:
=IF(ISERROR(MATCH("xyz",B1:B13,0)), "N/A", HYPERLINK("#"&ADDRESS(MATCH("xyz",B1:B13,0),2), "xyz" ))

Don't ask me why the second formula hyperlink works but does not show up as a hyperlink, I don't know.

You should look up ADDRESS Worksheet Function and the MATCH Worksheet Function in HELP for some details.

Using HYPERLINK Worksheet Function to also pull in associated link (#vlookup)

Add another column to your table with the hyperlink.  You can extract the hyperlink on your Lookup sheet with a User Defined Function hyperlinkaddress. . On lookup sheet
J1: =personal.xls!hyperlinkaddress(A1)

On your sheet with VLOOKUP
  =HYPERLINK(VLOOKUP(B8,Lookup!A1:Lookup!J1203,10,FALSE), VLOOKUP(B8,Lookup!A1:Lookup!J1203,3,FALSE) )

Additional Examples with mailto:...   (#mailto)

  =HYPERLINK("Mailto:" & B2, A2)
  =HYPERLINK("Mailto:" & B2,"[x]")
  =IF(ISERROR(SEARCH("@",B2)),"","mailto:" & HYPERLINK(B2))
  =IF(ISERROR(SEARCH("@",B2)),"",HYPERLINK("mailto:" & B2,"[x]"))

To extract a hyperlink see hyperlinkaddress and other User Defined Functions in buildtoc.htm#url.

Using HYPERLINK Worksheet Function where workbook name can change   (#internal)

As aready mentioned you must include the filename, even if it refers to a cell in the same workbook.  You can use this code to generate the workbook name.  As mentioned on my Pathname page you must include the cell reference to get a valid result from the CELL Worksheet Function. 

B3:  =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

and use it as follows:
A1:  =HYPERLINK("["&hyperlink!$B$3 & "]'Sheet One'!A1","Sheet One")

Update:
But as updated in blue above, the following would be used within same sheet and would allow renaming/moving workbook without changing formulas.  (works in XL97 and up)
  =HYPERLINK("#"&CELL("address",C5),C5)
or within same workbook
  =HYPERLINK("#"&CELL("address",sheet7!C5),sheet7!C5)

Link Back to Sheet (#followhyperlink)

Problem: One of the sheets in the workbook is named 'HelpMe', and the user wants to be able to use a link button or other means to get to that page and to return back where they came from.

Solutions:
If you use a hyperlink or if in a macro you use FollowHyperlink Address you use hyperlinkaddress you can use the web BACK key (Alt+ArrowLeft) to return. You can install the buttons on your toolbar from tools, customize, commands, Web, and drag the two buttons BACK and FORWARD to the toolbar. Invoke as a button on Worksheet
If your button is on the worksheet you can hyperlink to you help worksheet.  I don't think you can do that you can assign a hyperlink directly to a toolbar button without having it invoke a macro instead.

Invoke as a HYPERLINK Worksheet Function from the worksheet
  =HYPERLINK("[workboo14.xls]helpme!a5","help")
  =HYPERLINK("#helpme!E6","help")
  =HYPERLINK("#"Help Me'!A1","help")


Invoke as an object type hyperlink [Ctrl+K]
Object hyperlinks (Ctrl+K) will =change automatically if you rename the worksheets, but can be burdomsome on older versions of Excel. (Pre 2000 on Win 98).

Invoke link as a DoubleClick Event macro from the worksheet

  Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
    'to install -- right-click on the sheettab of the sheet to
    ' be used in and choose 'view code'.   Paste this Worksheet
    ' event macro into the module.
    Cancel = True   'Get out of edit mode
    Dim xx As String
    xx = "#'Help Me'!E6"
    ActiveWorkbook.FollowHyperlink Address:=xx, NewWindow:=False
  End Sub

Link to a Chart or Shape, faked by an Event Macro
Hyperlinks in Excel, Jon Peltier, some notes on hyperlinks including how to fake a link to a chart (or other shapes).

To Return to original worksheet:
As mentioned at the beginning you can return with the Keyboard Shortcut   ALT+ArrowLeft   or with the Toolbar BACK button that can be installed on your toolbar as previously described.

This topic was a little hard to decide whether to put on this webpage (sheets.htm), or on my Build Table of Contents webpage, or on Event Macros webpage as another example, or even perhaps the Shortcuts webpage because of the BACK button.

Link Excel into HTML or MS Word (#linkexcel)

for MS Word as C:\My Documents\excel\book1.xls#sheet1!c9
for HTML as <a href="c:\temp\mybook.xls#sheet1!c9">worksheet 1</a>

Can't get the above HTML link to work in Firefox, let alone with a space in the sheetname with Firefox.
  <a href="file:///C:\bk1\h\excel2k\2004-11.xls#Sheet16!d2">d2 in sheet 16</a> -- d2 in sheet 16

	'h:\excel2k\[vlookup.xls]sheet13'!C4

3-D references   (#d3)

The following will SUM the cells D3:G3 from the range of sheets as seen in the worksheet tabs at the bottom:  Sheet5:Sheet8
  ='sheet5':'sheet8"!D3:G3  

the following will SUM the cells D3:G3 in each of the sheets named in the cells L3:P3   (Dominic 2005-05-05, misc) -- You will get a #REF! error if any sheetname is missing or is invalid in the L3:P3 range.
  =SUMPRODUCT(SUMIF(INDIRECT("'"&L3:P3&"'!D3:G3"),"<>"))

Some Worksheet Examples using INDIRECT   (#indirect)

   ='Worksheet 1'!G3
   =INDIRECT("Worksheet 1'" & "!" & "G3")
   =INDIRECT("'Worksheet 1'" & "!G" & h4)
   =INDIRECT("'c:\My Documents\[WTA " & Year(Now())-1 & ".xls]Sheet1'!$B$4")
   =INDIRECT("["&D2&".xls]'Sheet1'!$A$1")

INDIRECT will not work for closed files.

Extended one step further using the sheetname in cell A1:

   =INDIRECT("'" & A1 & "'" & "!" & "G" & H4)

   =INDIRECT("'" & A1 & "'!G" & H4)

   =LARGE($A$1:$A$10,ROW(1:1))

INDIRECT to refer to a cell in another workbook (actually used same workbook, can you tell?)

Note cell A1 and E1 show a single quote but you must type in an additional single quote before the one you see.

another example:
Sheetnames are in Column C (cells C1:C50),  The sheets that hae a model number of "ModelA" in their A3 cell are to have the value of their H31 cells totalled in the formula. [Bob Phillips, .excel, 2006-05-31]
=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C50&"'!A3"),"ModelA",INDIRECT("'"&C1:C50&"'!H31")))

Additional examples of INDIRECT can be found on my Build Table of Contents page.

Last sheet Created   (#lastsheet)

     LastSheetName = Worksheet(Worksheets.Count))

Referencing a Relative Worksheet Name in a Workbook   (#relsheet)

Function RelSheet(sht As Long) As String
  RelSheet = Worksheets(sht).Name
End Function

Function WB_Sheet_cell(wb As String, sht As Long, cell As String) As String
  WB_Sheet_cell = Workbooks(wb).Worksheets(sht).Range(cell)
End Function

HTML HREF=   (#href)

my own test: cell A3

Workbook   (#workbook)

Open a workbook from named in cells

For Each cell In Range("A1", Range("a1").End(xlDown))
    Workbooks.Open (cell & ".xls")
Next

Conditionally Open a workbook

Dim oWB As Workbook  'posted by Bob Phillips, 2004-02-15, misc
    On Error Resume Next
        Set oWB = Workbooks("myBook")
    On Error GoTo 0
    If oWB Is Nothing Then
        Set oWB = Workbooks.Open("C:\myDir\myBook.xls")
    End If
    oWB.Worksheets("Sheet1").Activate

List the names of the open workbooks

Create folder and save file

Sub CreateFolderAndSaveFile()
'Nick Hodge, 2000-06-12 microsoft.public.excel.worksheet.functions
Dim fName As String
On Error Resume Next
fName = ThisWorkbook.Worksheets("Sheet1").Range("C3").Value
MkDir ("C:\Projects\" & fName & "\")
ChDir ("C:\Projects\" & fName & "\")
ThisWorkbook.SaveAs Filename:=fName
End Sub

Generate Sheets with sheetnames named from current selection of cells

Sub genWStabnames()
    Dim cell As Range
    Dim newName As String, xx As String
    Err.Description = ""
    On Error Resume Next
    For Each cell In Selection
       Worksheets.Add
       If Err.Description <> "" Then Exit Sub
       MsgBox Format(cell.Value, "yyyy-mm-dd")
       Err.Description = ""
       newName = Format(cell.Value, "yyyy-mm-dd")
       ActiveSheet.Name = newName
       If Err.Description <> "" Then
          '--failed to rename, probably sheetname already exists...
          xx = MsgBox("Failed to rename inserted worksheet " & _
           vbLf & _
           ActiveSheet.Name & " to " & newName & vbLf & _
           Err.Number & " " & Err.Description, vbOKCancel, _
           "Failure to Rename Worksheet:")
          '--eliminate already created sheet that was to be renamed...
          Application.DisplayAlerts = False
          ActiveSheet.Delete
          Application.DisplayAlerts = True
          '--check for immediae cancellation...
          If xx = vbCancel Then Exit Sub
          Err.Description = ""
       End If
    Next cell
End Sub

Miscellaneous   (#misc)

And if the macro is exited with any of these things in effect is there any way of telling what's wrong or just resetting it,

    Application.Interactive = False      'prevents user interference with the macro
    Application.DisplayAlerts = False  'suppress prompts and alerts
    Application.ScreenUpdating = False  'suppress screen repainting
    ActiveWindow.Visible = False     'hides the active window

"C:\Program Files\Microsoft Office\Office\Excel.exe" /regserver change path as needed or just use Excel.exe /regserver

291288 - Startup Switches for Microsoft Excel (2002: 291288, 2000: 211481; 97: 159474) (SAFE Mode, Re-register/Reregister Excel extensions)
  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q159474

Misc other short things

'Clear the clipboard, if copied from the worksheet
    Application.CutCopyMode = False

Worksheet Tabs, and Linking (#hyp_ws_tabs)

A link to a worksheet using the HYPERLINK Worksheet Formula will fail when the worksheet is renamed because it is a constant within double quotes.  A solution to this problem is to use a VBA Function.  See details in solution by Bill Manville, and in the extensive examples above in Hyperlink Worksheet References outside of quotes. 
    =HYPERLINK("#'sheet67'!a1","'sheet67'!A1)
    =HYPERLINK("#'" & XLName("Sheet67")& "'!$A$1",XLName("Sheet67")& "!$A$1")

  Function XLName(stVBASheet As String) As String
    Dim WS As Worksheet  'Bill Manville, 2003-02-20, links
    Application.Volatile
    For Each WS In Application.Caller.Parent.Parent.Worksheets
      If WS.CodeName = stVBASheet Then
        XLName = WS.Name
        Exit Function
      End If
    Next
  End Function

Relative Hyperlinks and Hyperlink Base (#relative)

With a need to hyperlink to a lot of files such as PDF files in the same directory as the Excel workbook, you could specify the pathname in a cell on a specific sheet and then use the HYPERLINK worksheet function.

But the use of the Hyperlink Base in workbook properties can be used effectively, see thes postings by Harlan Grove. 2002-04-22 and this one to access the hyperlink property 2002-04-26

Hidden worksheets (#hidden)

See VBE Help for "Visible Property" having to do with worksheets and there is also a xlveryhidden

 
Worksheets("Sheet1").Visible = False
Worksheets("Sheet1").Visible = True
sht.visible = True

Missing Scrollbars or Worksheet Tabs   (#missing)

• Tools --> Options --> View (tab) --> ...
   [x] Horizontal scroll bar
   [x] Vertical scroll bar
   [x] Sheet tabs [if still hidden then ...]
• The horizontal scrollbar may have been dragged to far to the left, drag the "Tab Split Box" between the sheet navigation keys and horizontal scrollbar to the right to expose the Horizontal sheet tabs normally seen between the navigation keys and the horizontal scrollbar.

Related Examples   (#related)

Things beginning with #-sign (#hash)

• Error messages:  see help, index, also see Errors page
    i.e. #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!, and for numbers or width ######
• Cell Reference:  To link to a cell or to a defined name,
    see Coding a link address and friendly name using HYPERLINK Worksheet function --#hyperlink above
    i.e. =HYPERLINK("#C5",C5)
• To link to a module:  see Hyperlink to a code module, Andy Wiggins describes for each Excel version using the right click menu, equivalent to...
    i.e. =HYPERLINK(

Build Table of Contents (#BuildTOC) primarily deals with Listing Sheetnames, hyperlinks, and other documentation things.

• Build Table of Contents, with optional sorting of sheets
• List of Functions and Subroutines
• List of worksheet names
• EnumerateAddIns() List the add-ins into an "AddinsSheet" in your workbook.
• SortALLSheets
• Menus and links for menus (Walkenbach, Pearson, and MS KB),
  on site material include Barhopper -- fixup for Restored Toolbars, and -- Listing of Menu Items.
• Shapes are documented on a separate webpage, Listing Shapes, getting hyperlinks of shapes, etc.

Build Table of Contents (#BuildTOC) also has many sheet related items, in addition to some of the above.

• Converting a selection containing sheetnames to hyperlinks   (buildtoc.htm#trevor001)
• GoToSheet -- Select the Sheet named in selected cell   (buildtoc.htm#GoToSheet)
• List of A1 Cells by Sheetname   (buildtoc.htm#GetAllA1Cells)
• List Sheet Names   (buildtoc.htm#EnumerateSheets_XL95)
• Looping through a set of workbooks listed in a range   (buildtoc.htm#loopWB)
• Looping through all of the worksheets   (buildtoc.htm#SheetnamesInA1)
• Names of WorkSheets across columns from the activecell   (buildtoc.htm#across)
• Navigating to Previous, Next or specific sheet     (buildtoc.htm#navigate)
• Navigation to a Sheetname with a popup (#moresheets)
• Navigating within a Sheet   (buildtoc.htm#navigatewithin)
• Place active cell in top left of viewable area   (buildtoc.htm#showtopleft)
• Remove CRLF characters from ClipBoard   (buildtoc.htm#RemoveCRLF)
• Repositioning screen and/or active cell   (buildtoc.htm#TopLeft)
• Running a macro for all worksheets in a workbook   (buildtoc.htm#allworksheets)
• RunSubFromActiveCell   (buildtoc.htm#RunSubFromActiveCell)

More links in buildtoc.htm on hyperllinks (#morehyperlinks)

• Alternative to preventing generation of a hyperlink (buildtoc.htm#preventhyp)
• Creating Hyperlinks for all Cells in a Selection (buildtoc.htm#MakeHyperlinks)
• Fix Hyperlinks (buildtoc.htm#FixHyperlinks)
• Function to show hyperlink URL used in another cell (buildtoc.htm#url)
• More notes on HYPERLINKs (buildtoc.htm#hyperlink)
• Removing object hyperlinks from a Cell or cell range (buildtoc.htm#DelHyperlinks)
• Simulating a Hyperlink via Macro (buildtoc.htm#GoToHyperlink)

Master Sheet, creation   (#mastersheet)

• Build Table of Contents, similar listings, working with Hyperlinks and less macro oriented variation Build TOC Another Approach.  On this page see Looping through sheets
• Ron de Bruin has a web page specifically for a Master Sheet generation:  Copy cells from each sheet in the workbook, topics include:  Copy a range of each sheet, Copy the CurrentRegion of a cell of each sheet, Copy the UsedRange of each sheet, Copy a row or rows of each sheet, Copy from row? till the last row with data, and Copy a column or columns from each sheet.

Cells, Cells related coding

PathName

Slow Response

• Some items to speed up Excel
• To create a button on toolbar see my page -- Toolbars and Custom Buttons

Related Information (#related_info)

• Build Table of Contents, listing of sheetnames and sheet codenames (see my site above).
• Check Existence of Sheet or sheet and WB and also, Chip Pearson, also see just for WS above (on this page).
• Create worksheets based on names in Column B, Robert Rosenberg, includes error recovery if sheet already exists.
• Collections, including Sheets collection, and references to hiding and unhiding worksheets (Hide and Unhide sheets).
• File Access with Visual Basic® for Applications, describes how to process files with the file input/output (I/O) functions that Visual Basic for Applications provides -- information on using Sequentiai, Random access, and Binary.  The examples show are for a .CSV type of file for Firstname, Lastname, etc. 
• Grouping worksheets, to make manual changes to several sheets at one time.
• Grouping worksheets via an Array, which could be doing more refined selections, Jim Rech, ~2003-08-15, programming
• Importing Data into Excel (.CSV, .html) import.htm -- rough sketch.
• Menu, Worksheets into a, Chip Pearson, see entire thread.
• Move, Copy, Replace, Populate
• Optimizing VBA, Chip Pearson
• Pathname in headings, footers, and cells
• Print tips for Excel, Ron de Bruin
• Referencing Worksheets From Formulas (C. Pearson), VBA procedure that you can use to refer to the first, next, previous, or last worksheet in a workbook, called directly from worksheet cells.
• Summary of Methods for Navigating between worksheets that you will find here in "My Excel Pages".
• Create a weekly_chronology.xls (D.McRitchie, 2007-07-12)

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved