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.
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() 'Declaration Dim WbMacro As Workbook Dim ShMenu As Worksheet Dim ShData As Worksheet Dim ShResults As Worksheet Dim ShAnalysis As Worksheet 'Initialisation 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 ShMenu.Activate 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 Worksheet 'Initialisation 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 TabName = ActiveSheet.Name 'Retrieve the name of the tab Sheets(TabName).Copy After:=ShMenu TempWb.Close SaveChanges:=False 'Close the temporary wbk 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
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
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
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
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() Sheets("Weather").Activate ActiveWindow.Zoom = 80 ActiveWindow.DisplayGridlines = False End Sub
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
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
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
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 Sheets("Weather").Delete Application.DisplayAlerts = True End Sub
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 Sh.Delete End If Next Sh Application.DisplayAlerts = True End Sub