Worksheets and Tabs

Working With Worksheets and Tabs


Add worksheet Options

Following are various options to add a worksheet to a workbook. One determining factor is where you want it placed within the workbook.

    Sheets.Add
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add Before:=ActiveSheet
    Sheets.Add After:=ActiveSheet

Rename current Sheet

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
ActiveSheet.Name = "Renamed Sheet"
ActiveSheet.Name = "D" & Format(Date, "yyyymmdd")
ActiveSheet.Name = "D" & Format(Range("A1"), "yyyymmdd")
ActiveSheet.Name = "D" & Format(Range("A1"), "yyyy_mmdd_hhmm")
[/sourcecode]

Delete a sheet

You can specify which sheet to delete, or delete the active sheet with the following code.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Application.DisplayAlerts = False

Sheets("SheetName").Delete ‘Use to delete specific sheet
Activesheet.Delete ‘Used to delete the current sheet
ActiveWindow.SelectedSheets.Delete ‘Used to delete multiple sheets

Application.DisplayAlerts = True
[/sourcecode]

Cycling through worksheets

This macro will change the name and color of the first two tabs in the workbook.
[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub nameAndColor()
‘first sheet in workbook
ActiveSheet.Select
ActiveSheet.Name = "test1"
With ActiveWorkbook.ActiveSheet.Tab
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399975585192419
End With
‘next sheet to the right
ActiveSheet.Next.Select
ActiveSheet.Select
ActiveSheet.Name = "test2"
With ActiveWorkbook.ActiveSheet.Tab
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
End With
End Sub

[/sourcecode]

Add worksheet and name it with the same code

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sheets.Add.Name = "SheetName" ‘Add sheet and name it to TabName
[/sourcecode]

Or

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
ActiveWorkbook.Worksheets.Add(After:=Activesheet).Name = "NewSheet"
[/sourcecode]
Or
[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Private Sub AddNameNewSheet1()
‘Doesn’t check for errors
Dim Newname As String
Newname = inputBox("Name for new worksheet?")
If Newname <> "" Then
Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = Newname
End If
End Sub
[/sourcecode]


Cycling through worksheets

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub CycleWorksheets()
For i = 1 To Worksheets.count
Worksheets(i).Activate
‘“ add code to do on each worksheet here”
i = i + 1
Next i
End Sub
[/sourcecode]


Sheet Sorter routine

Be sure to copy both the SortSheets and BubbleSort macros to a module when using this sheet sorter routine. The BubbleSort macro is called from the SortSheet as part of the process.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Option Explicit

Sub SortSheets()
‘ This routine sorts the sheets of the active workbook in ascending order.
‘ Use Ctrl+Shift+S to execute

Dim SheetNames() As String
Dim i As Long
Dim SheetCount As Long
Dim OldActive As Object

If ActiveWorkbook Is Nothing Then Exit Sub ‘ No active workbook
SheetCount = ActiveWorkbook.Sheets.Count

‘ Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is protected.", _
vbCritical, "Cannot Sort Sheets."
Exit Sub
End If

‘ Make user verify
If MsgBox("Sort the sheets in the active workbook?", _
vbQuestion + vbYesNo) <> vbYes Then Exit Sub
Application.EnableCancelKey = xlDisabled ‘ Disable Ctrl+Break
SheetCount = ActiveWorkbook.Sheets.Count ‘ Get the number of sheets
ReDim SheetNames(1 To SheetCount) ‘ Redimension the array
Set OldActive = ActiveSheet ‘ Store a reference to the active sheet
For i = 1 To SheetCount ‘ Fill array with sheet names
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
Call BubbleSort(SheetNames) ‘ Sort the array in ascending order
Application.ScreenUpdating = False ‘ Turn off screen updating
For i = 1 To SheetCount ‘ Move the sheets
ActiveWorkbook.Sheets(SheetNames(i)).Move _
Before:=ActiveWorkbook.Sheets(i)
Next i
OldActive.Activate ‘ Reactivate the original active sheet
End Sub

Sub BubbleSort(List() As String)
Dim First As Long, Last As Long
Dim i As Long, j As Long
Dim Temp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last – 1
For j = i + 1 To Last
If UCase(List(i)) > UCase(List(j)) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub
[/sourcecode]

Limit Scroll Area

To limit the scroll area on a worksheet, add the following code into the “This Workbook” object. This can be changed based on what area you want to have accessable to the workbook user. This macro allows the user access to A1:F10 of “Sheet 1″.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "a1:f10"
End Sub
[/sourcecode]