Working with Multiple Worksheets in VBA

An Excel workbook can contain many worksheets.

For example, a workbook may contain:

• Students
• Attendance
• Fees
• Results
• Reports

VBA can work with all these worksheets automatically.

The Worksheets collection represents the worksheets in a workbook.

Worksheets

You can use it to access individual worksheets or loop through all worksheets.

A worksheet can be referenced by its name.

Worksheets("Students")

This refers to the worksheet named Students.

Worksheets can also be referenced by their position number.

Worksheets(1)

This refers to the first worksheet in the workbook.

Worksheets(2)

This refers to the second worksheet.

The Activate method makes a worksheet active.

Worksheets("Students").Activate

The Students worksheet becomes the active worksheet.

A worksheet can also be selected.

Worksheets("Students").Select

This selects the Students worksheet.

In most automation tasks, direct references are preferable to unnecessary selection.

You can read data from a different worksheet without activating it.

Dim studentName As String

studentName = Worksheets("Students").Range("B2").Value

MsgBox studentName

This reads the value from B2 on the Students worksheet.

VBA can write data directly to another worksheet.

Worksheets("Reports").Range("A1").Value = "Student Report"

The text is written into A1 of the Reports worksheet.

A worksheet can be stored in an object variable.

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Students")

ws.Range("A1").Value = "Student ID"

The variable ws now refers to the Students worksheet.

You can work with multiple worksheets in the same procedure.

Dim wsStudents As Worksheet
Dim wsReports As Worksheet

Set wsStudents = ThisWorkbook.Worksheets("Students")
Set wsReports = ThisWorkbook.Worksheets("Reports")

wsReports.Range("A1").Value = wsStudents.Range("A1").Value

The value from Students!A1 is copied to Reports!A1.

A range can be copied from one worksheet to another.

Worksheets("Students").Range("A1:D10").Copy _
Destination:=Worksheets("Reports").Range("A1")

The range A1:D10 from Students is copied to Reports starting at A1.

Sometimes you only want the values and not the formatting or formulas.

Worksheets("Reports").Range("A1:D10").Value = _
Worksheets("Students").Range("A1:D10").Value

This transfers the values directly between the two ranges.

A new worksheet can be created using the Add method.

Worksheets.Add

Excel adds a new worksheet to the workbook.

You can create a worksheet and immediately give it a name.

Dim ws As Worksheet

Set ws = Worksheets.Add

ws.Name = "New Report"

A new worksheet named New Report is created.

The Name property can be used to rename an existing worksheet.

Worksheets("Sheet1").Name = "Students"

Sheet1 is renamed to Students.

The Count property tells you how many worksheets are in the workbook.

Dim totalSheets As Long

totalSheets = ThisWorkbook.Worksheets.Count

MsgBox totalSheets

The number of worksheets is displayed.

A For Each loop can process every worksheet.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    MsgBox ws.Name

Next ws

This displays the name of every worksheet.

You can apply the same formatting to all worksheets.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    ws.Rows(1).Font.Bold = True
    ws.Columns("A:D").AutoFit

Next ws

The first row of every worksheet becomes bold and columns A:D are autofitted.

The same information can be written to several worksheets.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    ws.Range("A1").Value = "Generated by VBA"

Next ws

Each worksheet receives the same text in A1.

You can use an If statement to skip a worksheet.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> "Reports" Then

        ws.Range("A1").Value = "Processed"

    End If

Next ws

The Reports worksheet is skipped.

The Copy method can duplicate a worksheet.

Worksheets("Students").Copy After:=Worksheets("Students")

A copy of the Students worksheet is created after the original.

A worksheet can be moved to another position.

Worksheets("Reports").Move Before:=Worksheets(1)

The Reports worksheet is moved before the first worksheet.

The Visible property can be used to hide a worksheet.

Worksheets("Students").Visible = False

The Students worksheet becomes hidden.

To make it visible again:

Worksheets("Students").Visible = True

A common Excel project may have separate worksheets for raw data and reports.

Dim wsData As Worksheet
Dim wsReport As Worksheet

Set wsData = ThisWorkbook.Worksheets("Students")
Set wsReport = ThisWorkbook.Worksheets("Reports")

wsReport.Range("A1").Value = "Student Name"
wsReport.Range("B1").Value = wsData.Range("B2").Value

The report receives information from the Students worksheet.

The following example reads student information from the Students worksheet and writes it to the Reports worksheet.

Sub CreateStudentReport()

    Dim wsStudents As Worksheet
    Dim wsReports As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set wsStudents = ThisWorkbook.Worksheets("Students")
    Set wsReports = ThisWorkbook.Worksheets("Reports")

    wsReports.Range("A1:C1").Value = _
        Array("Student ID", "Student Name", "Course")

    lastRow = wsStudents.Cells(wsStudents.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        wsReports.Cells(i, 1).Value = wsStudents.Cells(i, 1).Value
        wsReports.Cells(i, 2).Value = wsStudents.Cells(i, 2).Value
        wsReports.Cells(i, 3).Value = wsStudents.Cells(i, 3).Value

    Next i

    wsReports.Columns("A:C").AutoFit

    MsgBox "Report Created Successfully"

End Sub

• Using the wrong worksheet name.
• Using an incorrect worksheet index.
• Forgetting to qualify Range or Cells with the worksheet.
• Trying to access a worksheet that does not exist.
• Accidentally modifying the active worksheet.
• Using Select unnecessarily.
• Renaming a worksheet to a name that already exists.
• Deleting or moving the wrong worksheet.

The following example processes several worksheets while skipping the Reports worksheet.

Sub ProcessWorksheets()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        If ws.Name <> "Reports" Then

            ws.Rows(1).Font.Bold = True
            ws.Columns("A:D").AutoFit

        End If

    Next ws

    MsgBox "Worksheets Processed Successfully"

End Sub

• Use worksheet variables for frequently used worksheets.
• Prefer worksheet names when the workbook structure is stable.
• Use ThisWorkbook when you want the workbook containing the VBA code.
• Qualify Range and Cells with the appropriate worksheet.
• Check worksheet names before accessing them.
• Avoid unnecessary Select and Activate operations.
• Use For Each when the same operation must be applied to many worksheets.
• Keep report and source worksheets clearly organized.

A typical multiple-worksheet workflow is:

1. Identify the workbook.
2. Identify the required worksheets.
3. Store frequently used worksheets in variables.
4. Read or write data using worksheet-qualified references.
5. Loop through worksheets when required.
6. Format or generate reports.
7. Save the workbook after completing the automation.

Dim wsData As Worksheet
Dim wsReport As Worksheet

Set wsData = ThisWorkbook.Worksheets("Students")
Set wsReport = ThisWorkbook.Worksheets("Reports")

wsReport.Range("A1").Value = wsData.Range("A1").Value

Working with multiple worksheets is an essential part of Excel VBA. A workbook can contain different worksheets for different purposes, and VBA can move data between them automatically.

A worksheet can be referenced by name:

Worksheets("Students")

It can also be stored in a variable:

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Students")

Multiple worksheets can be processed using a loop:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    ws.Columns("A:D").AutoFit

Next ws

You can also transfer information between worksheets:

Worksheets("Reports").Range("A1").Value = _
Worksheets("Students").Range("A1").Value

These techniques are useful for creating student management systems, attendance reports, fee reports, result systems, dashboards, and other practical Excel VBA projects.