index

1-Referencing a sheet - Item()

2-To programmatically rename a worksheet

3-To programmatically select a worksheet

4-Move a worksheet,

5-To move worksheet to a new file

6-Add worksheet

7-Delete Worksheet

8-Random number generating

9-Saving a Worksheet as a Web Page

1-Referencing a sheet - Item()

For the second sheet

Private Sub CommandButton1_Click()
    Sheets.Item(2)
End Sub

2-To programmatically rename a worksheet

Access the Name property of the collection and assign the desired name. Here is an example:

Private Sub cmdRename_Click()
    Sheets("Sheet1").Name = "Employees Records"
End Sub

3-To programmatically select a worksheet

Here is an example that assumes that there is a worksheet named Reimbursement on the current workbook:

Private Sub cmdRename_Click()
    Sheets("Reimbursement").Select
End Sub

4-Move a worksheet,

To programmatically move a worksheet, use the Move() method of its collection. The syntax of this method is:

format : Worksheets.Move(Before, After)

Private Sub cmdMove_Click()
    Worksheets("Sheet3").Move After:=Worksheets("Sheet1")
End Sub

5-To move worksheet to a new file

the name of the worksheet you are passing as argument must exist

Private Sub CommandButton1_Click()
    Sheets.Item("Sheet2").Move
End Sub

Or

Private Sub CommandButton1_Click()
    Sheets.Item(3).Move
End Sub

6-Add worksheet

To create a new worksheet using code, you can specify whether you want it to precede or succeed an existing worksheet.

Call the Add() method of the Sheets collection. Its syntax is:

Sheets.Add(Before, After, Count, Type)

All of these arguments are optional. This means that you can call this method as follows:

Private Sub cmdNewWorksheet_Click()
    Sheets.Add
End Sub

If you call the method like that, a new worksheet would be created and added to the left side of the active worksheet. If you want to create a new worksheet on the left side of any worksheet you want, you can first select that worksheet and call the Add() method. For example, suppose you have three worksheets named Sheet1, Sheet2, and Sheet3 from left to right and you want to insert a new worksheet between Sheet2 and Sheet3, you can use code as follows:

Example 1 :

Private Sub cmdNewWorksheet_Click()
    Sheets("Sheet2").Select
    Sheets.Add
End Sub

Example 2 : 

Private Sub cmdNewWorksheet_Click()
    Worksheets.Add Before:=Worksheets("Sheet3")
    Worksheets(Worksheets.Count - 1).Name = txtNewWorksheet.Text
    txtNewWorksheet.Text = ""
End Sub

7-Delete Worksheet

Note: the sheet name placed in the text box txtRemoveSheet is deleted

Private Sub cmdRemoveSheet_Click()
    Worksheets(txtRemoveSheet).Delete
    txtRemoveSheet.Text = ""
End Sub

8-Random number generating

Dim qwery As Integer

 qwery1 = Int(Abs(1 + Rnd * 100))

9-Saving a Worksheet as a Web Page

click File -> Web Page Preview

File -> Save As Web Page

Click Publish