Skip to content

Working With Worksheets in Excel VBA

    Select a Specific Worksheet

    Worksheets("Expenditure 2016").Select

    Refer to the Active Worksheet Using ActiveSheet

    ActiveSheet.Range("A10").Interior.ColorIndex = 7

    Activate a Worksheet

    Worksheets("Sheet1").Activate

    Create a New Worksheet/s

    Worksheets.Add before:=Worksheets("Sheet4"), Count:=5

    Name a Worksheet

    Name the active sheet

    ActiveSheet.Name = "New Name"

    Name a specific sheet

    Worksheets("Expenditure 2016 Draft").Name = "Expenditure 2016 Final"

    Change a Worksheet’s Tab Colour

    Worksheets("Sheet1").Tab.ColorIndex = 7

    Delete Worksheet

    ‘Disable dialog boxes
Application.DisplayAlerts = False
Worksheets("Sheet10").Delete
‘Enable dialog boxes
Application.DisplayAlerts = True

    Copy a Worksheet

    Worksheets("Sheet1").Copy before:=Worksheets("Sheet2")

    To copy multiple sheets…

    Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy before:=Worksheets("Sheet4")

    Copy to a different workbook

    ActiveWorkbook.Worksheets("Sheet1").Copy before:=Workbooks("Book1").Worksheets("Sheet1")

    Move a Worksheet

    Worksheets("Sheet1").Move after:=Worksheets("Sheet4")

    To move multiple sheets

    Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Move _ after:=Worksheets("Sheet4")

    Move to a different workbook

    ActiveWorkbook.Worksheets("Sheet1").Move _ before:=Workbooks("Book1").Worksheets("Sheet1")

    Group Worksheets and Do Stuff

    Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Worksheets("Sheet1").Activate
Range("A1").Select
Selection = "PRODID"
Range("B1").Select
Selection = "Sales"

    Note how you have to select the cell with one line of code and the enter the value with a separate line of code.  Doing otherwise causes a run-time error.

    Hide/Unhide a Worksheet

    Worksheets("Sheet2").Visible = False

    Protect a Worksheet

    Worksheets("Sheet1").Protect Password:="Badpassword"

    Use the Unprotect method to unprotect a sheet.

    Print a Worksheet

    Worksheets("Sheet1").PrintOut From:=1, To:=3, Copies:=5

    Links Cells Between Worksheets

    Worksheets("Sheet2").Range("A10") = Worksheets("Sheet1").Range("A10")

    Count the Number of Worksheets

    MsgBox "There are " & Worksheets.Count & " worksheets in this workbook."

    Display Page Breaks

    Worksheets("Expenditure 2016").DisplayPageBreaks = True

    Featured image https://www.flickr.com/photos/zemlinki/

     

    Tags: