This page helps to understand some useful notions about sheet objects in VBA. Sheets are presented in the Excel tool as tabs. A sheet has since the 2007 version of Excel 1,048,576 rows for 16,384 columns.

Last Row Excel 1048576 ans last column 16384

To manipulate a worksheet in a VBA macro, you can use the Sheet or Worksheet object in the Worksheets collection.

Add a new sheet

When you launch a macro to create a report or an analysis from data, I advise you to create your macro buttons on a sheet then the macro will create in a new workbook, or the same workbook of new sheet with your analysis .

To create a new sheet use the Add method of the Sheets collection.

By adding a new sheet, you can specify its place using the After or Before arguments. If nothing is specified on these arguments, then the new sheet will be automatically after the active sheet.

Don’t forget to use the Set tool to match all objects in your code with a declared variable.

Sub CreateNewSheet()
     Dim WbMacro As Workbook
     Dim ShMenu As Worksheet 
     Dim ShData As Worksheet
     Dim ShResults As Worksheet
     Dim ShAnalysis As Worksheet
     Set WbMacro = ActiveWorkbook
     Set ShMenu = WbMacro.Sheets("MENU")
     'One method
     WbMacro.Sheets.Add Before:=WbMacro.Sheets(ShMenu.Name)
     Set ShAnalysis = WbMacro.ActiveSheet
     ShAnalysis.Name = "Analysis" 'Rename the tab
     'Another method with one line of code
     Set ShData = WbMacro.Sheets.Add(After:=WbMacro.Sheets(ShAnalysis.Name))
     ShData.Name = "WEATHER DATA" 'Rename the tab
     'Add the new sheet after the last sheet using the Count property
     Set ShResults = WbMacro.Sheets.Add(After:=WbMacro.Sheets(WbMacro.Sheets.Count))
     ShResults.Name = "RESULTS" 'Rename the tab
End Sub

Import a sheet in a workbook saved in your computer

The procedure below allows you to import a sheet located in a workbook. Using the button, as in the project explorer article, you can ask the user to select the desired file. Then using the Open method of the workbook object, you can open the desired workbook and copy-paste the desired table.

Sub ImportSheet() 
'To import a sheet from another workbook

    Dim WbMacro As Workbook
    Dim ShMenu As Sheets

    Set WbMacro = ActiveWorkbook
    Set ShMenu = WbMacro.Sheets("MENU")

    FilePathName = Range("D6").Value 'Path of the file
    Filename = Range("D7").Value 'Name of the file

    Workbooks.Open Filename:=PathName & Filename 'Open the workbook
    Set TempWb = ActiveWorkbook 'Initialise the variable
    ActiveSheet.Name = TabName 'Retrieve the name of the tab

    Sheets(TabName).Copy After:=WbMacro.Sheets(ShMenu.Name)
    TempWb.Close SaveChanges:=False 'Close the temporary wbk
    WbMacro.ShMenu.Activate 'Activate the menu sheet

End Sub

Copy / Paste / Rename a sheet

The procedure below shows how you can copy and paste and rename a sheet in VBA. All methods Copy, Name and Tab.Color are attached to the sheet object.

Sub CopyPasteAndRenameTab()

    'Copy-paste the tab
    Sheets("Menu").Copy After:=Sheets("Menu")

    'Rename the Tab
    ActiveSheet.Name = "New Copy Tab"
    'Change the colour of the tab
    Sheets("New Copy Tab").Tab.Color = RGB(255, 94, 32)

End Sub
Colour tab

Move a sheet

To reclass your sheet automatically with VBA, you can use the Move property by stipulating the order if you prefer to put your sheet after or before another one.

Sub MoveSheets()
'Reclass your sheets

    Sheets("Jedi").Move after:=Sheets("Sith")
    Sheets("Sith").Move before:=Sheets("Yoda")

End Sub
Sort tab in the workbook

Print a sheet

To print a sheet, use the PrintOut method as for printing a workbook. This method take has arguments the following values: From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFilename, IgnorePrintAreas…

Sub PrintSheet ()
'Print a sheet

     Sheets("Games").PrintOut From:=1, To:=2, Copies:=2

End Sub
Print sheet or workbook

Sort sheets in alphabetical order in VBA

To navigate in the workbook more efficiently, it may be useful to reclassify your sheets efficiently. To find your way around, nothing better than sort sheets in alphabetical order. We are therefore going to re-sort the sheets by looping on the tabs and using the move property.

Sub SortSheetsTabs()
'Sort all sheets in alphabetic order
    'Deactivate the screen updating
    Application.ScreenUpdating = False
    Dim NbSh, i, j As Integer
    NbSh = Sheets.Count 'Give the number of sheet in the wbk
    For i = 1 To NbSh - 1
        For j = i + 1 To NbSh
            If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
                Sheets(j).Move before:=Sheets(i)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub
Sheet sorted

Define zoom and view of gridlines

If you need to see more data on your screen, one option is to shrink the zoom which by default is 100% on the sheet. You will be able to see more rows and columns with an 80% zoom while remaining readable. The Excel sheet has by default a grid to separate the different cells, you can also deactivate this grid if you want to design your borders.

Sub ChangeGridlineAndZoom()


   ActiveWindow.Zoom = 80
   ActiveWindow.DisplayGridlines = False

End Sub

Define zoom and gridlines

Display object method

Several methods of the ActiveWindows object will applied on a sheet object to display or not several properties.

Sub BooleanDisplayOrNot()
    'Boolean values True or False
    ActiveWindow.DisplayFormulas = True 'To not display formulas
    ActiveWindow.DisplayGridlines = False 'To not display gridlines
    ActiveWindow.DisplayHeadings = False 'To not display headings
    ActiveWindow.DisplayOutline = True 'Display the outline symbols
    ActiveWindow.DisplayZeros = False 'To not display zeroes in the sheet
    ActiveWindow.DisplayHorizontalScrollBar = True 'To display the Scroll Bar
    ActiveWindow.DisplayVerticalScrollBar = True 'To display the Scroll Bar
    ActiveWindow.DisplayRightToLeft = True 'To display the right to left   
    ActiveWindow.DisplayWorkbookTabs = True 'To display other tabs 
    ActiveWindow.DisplayWhitespace = True 'To display the whitespace 
    ActiveWindow.DisplayRuler = True 'To diplay the rules
End Sub

Loop on all sheets in VBA

Most of the time you need to move from one sheet to another because the user data are in different places. For this, it is necessary to be able to move on the sheets of your workbook by looping on tabs.

Sub LoopSheets()
    Dim Sh As Worksheet

    'Loop on all sheets in the wkb
    For Each Sh In ActiveWorkbook.Sheets

            'Write on all sheets
            Sh.Range("A1").Value2 = "Hello"

            'Clear all sheets if "Hello" is on A1
            If Sh.Range("A1").Value2 = "Hello" Then
                Sh.Cells.Clear ' Clear sheets
            End If

    Next Sh

End Sub

List the name of all sheets

Using a loop on every sheet of the workbook, we will write the name of each sheet in the first sheet.

Sub ListSheets()

    Dim Sh As Worksheet
    Dim i As Integer
    i = 1
    'Loop on all sheets to get names
    For Each Sh In Worksheets
        ActiveSheet.Cells(i, 1) = Sh.Name
        i = i + 1
    Next Sh
End Sub
List name sheets

Hide / Unhide sheet

As with the rows and columns of a sheet, it is also possible to hide and unhide the sheets from a workbook.

Used well, this ability to hide and unhide can also give users the feeling of navigation, such as on an application or a web page. Imagine that the user clicks on a button and changes page, you will simply have to give the order in the macro to hide the sheet on which he has just clicked, activate the second sheet by unhiding it.

Sub HideOrUnhide()
    Dim Sh As Worksheet

    'Hide all sheets except the sheet named "Menu"
    For Each Sh In ActiveWorkbook.Sheets
        If Sh.Name <> "Menu" Then Sh.Visible = False
    Next Sh

End Sub
Hide or unhide sheet

Protect a sheet with a password

For confidentiality reasons, it may be useful to lock an excel sheet of a workbook by using a password. At the time of protection, in addition to specifying the password, you must specify all the actions you want to block (select locked cells, use filter, use PivotTable …). Below the VBA code which can protect a workbook with the Protect method.

Sub ProtectSheet()
'Lock the sheet with a password

    ThisWorkbook.Sheets("Menu").Cells.Locked = True
    Sheets("Accounting").Protect "azerty", True, True
End Sub

Unprotect a sheet with a password

At the same way, if you are in front of a protect tab, you can unprotect the sheet using the right password with the Unprotect method.

Sub UnProtectSheet()
'Unlock the sheet with the required password

    Sheets("Accounting").Unprotect "azerty"
End Sub

Protect all sheets with a password

If your need is to protect all sheets in your workbook, by using the VBA method Protect you just need to loop on all tabs in the workbook.

Sub ProtectAllSheets()
'Lock all sheets with a password

    Dim NumberSheets, i As Long

    'Count the number of sheet in the wbk
    NumberSheets = Application.Sheets.Count
    For i = 1 To NumberSheets
        Sheets(i).Protect "password123", True, True
    Next i
End Sub

Unprotect all sheets with a password

To unprotect all sheets, loop on each sheet and use the VBA Unprotect method with the right password.

Sub UnprotectAllSheets()
'Unlock all sheets with the required password

    Dim NumberSheets, i As Long

    NumberSheets = Application.Sheets.Count
    For i = 1 To NumberSheets
        Sheets(i).Unprotect "password123"
    Next i
End Sub
Protect or unprotect sheet with a password

VBA Function to test if a sheet exists

Below a VBA function which can test if a sheet exists or not in your workbook. This function takes as parameter the name of the searching sheet as a string and the workbook.

Public Function ExistSheet(ShName As String, Wb As Workbook) As Boolean

    Dim Sh As Worksheet
    ExistSheets = False
    For Each Sh In Wb.Sheets
        If Sh.Name = ShName Then ExistSheets = True
    Next Sh

End Function

Delete a sheet

Erasing a sheet can be easily done using the delete property.

Sub DeleteTab()

    Application.DisplayAlerts = False


    Application.DisplayAlerts = True

End Sub
Delete a sheet

Restart your macro by deleting all sheets

In the menu options of your macro, it is useful to insert a button allowing to restart the launch and which will delete all the sheets created except the essential sheets, like your menu.

You can add deletions of values to cells, for example, places where file paths have been added.

Sub RestartMacro()

    Application.DisplayAlerts = False
    Dim Sh As Worksheet
     'Sheets("MENU").Range("D5").Value2 = ""

    'Delete all sheets except the MENU and the Userguide
    For Each Sh In Wb.Sheets
        If Sh.Name <> "MENU" And  Sh.Name <> "USERGUIDE" Then 
        End If
    Next Sh

    Application.DisplayAlerts = True

End Sub

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s