Example:

Sub PrintWorksheetNames()
    Dim ws As Worksheet

    'Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Sheets
        Debug.Print ws.Name
    Next ws
End Sub

Explanation:

The code defines a subroutine named PrintWorksheetNames.
It declares a variable ws of type Worksheet, which will be used to loop through each worksheet in the active workbook.
The For Each loop iterates through each worksheet in the ThisWorkbook.Sheets collection.
The Debug.Print statement is used to print the name of each worksheet in the Immediate window, which can be accessed by pressing Ctrl + G.

Example:

Sub InsertAndFillRow()
    Rows(2).Insert Shift:=xlDown
    Range("A2").Value = "John"
    Range("B2").Value = 25
    Range("C2").Value = "Sales"
End Sub

Explanation:

The code defines a subroutine named InsertAndFillRow.
Rows(2).Insert Shift:=xlDown inserts a new row at the second row and shifts the existing rows down to make space for the new row.
Range("A2").Value = "John" sets the value "John" in cell A2.
Range("B2").Value = 25 sets the value 25 in cell B2.
Range("C2").Value = "Sales" sets the value "Sales" in cell C2.

Example:

Sub AddDataValidation()
    With Range("A1").Validation
        .Delete 'Clear any existing validation
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=1, Formula2:=100
        .ErrorMessage = "Enter a whole number between 1 and 100."
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

Explanation:

The code defines a subroutine named AddDataValidation.
The With statement is used to work with the data validation settings for cell A1.
The existing validation (if any) is cleared using the Delete method.
The Add method is used to add new data validation settings to the cell.
Type:=xlValidateWholeNumber specifies that only whole numbers are allowed.
AlertStyle:=xlValidAlertStop sets the alert style to stop, which displays an error message when an invalid value is entered.
Operator:=xlBetween indicates that the data should be between two values.
Formula1:=1 and Formula2:=100 set the lower and upper bounds for the data validation.
The ErrorMessage property is used to set the error message that appears when an invalid value is entered.
IgnoreBlank = True allows blank cells without triggering an error.
InCellDropdown = True enables the drop-down arrow in the cell for easy data selection.

Example:

Sub CopyPasteValues()
    Dim sourceRange As Range
    Dim destinationRange As Range

    'Set the source and destination ranges
    Set sourceRange = Range("A1:A5")
    Set destinationRange = Range("B1:B5")

    'Copy and paste values from source to destination
    sourceRange.Copy
    destinationRange.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Explanation:

The code defines a subroutine named CopyPasteValues.
It declares two variables sourceRange and destinationRange, representing the ranges from which to copy values and where to paste them.
The Set statement is used to define the source range (A1:A5) and the destination range (B1:B5).
The Copy method is used to copy the values from the sourceRange to the clipboard.
The PasteSpecial method is used to paste only the values from the clipboard to the destinationRange.
Application.CutCopyMode = False clears the clipboard and ends the copy mode.

Example:

Sub ProtectUnprotectWorksheet()
    Dim ws As Worksheet
    Dim password As String

    'Set the password for protection
    password = "mypassword"

    'Get the reference to the active worksheet
    Set ws = ActiveSheet

    'Protect the worksheet with password
    ws.Protect Password:=password

    'Wait for 2 seconds
    Application.Wait (Now + TimeValue("00:00:02"))

    'Unprotect the worksheet with password
    ws.Unprotect Password:=password
End Sub

Explanation:

The code defines a subroutine named ProtectUnprotectWorksheet.
It declares a variable ws of type Worksheet and a variable password to store the password used for protection.
The password "mypassword" is set using the password variable.
The ActiveSheet property is used to get a reference to the currently active worksheet, which will be protected and unprotected.
The Protect method is used to protect the worksheet with the specified password.
The Application.Wait function is used to introduce a delay of 2 seconds before unprotecting the worksheet. This allows time to observe the protected state.
Finally, the Unprotect method is used to unprotect the worksheet with the same password.

Example:

Sub ColorCellsBasedOnValue()
    Dim cell As Range

    'Loop through each cell in column B
    For Each cell In Range("B1:B10")
        If cell.Value > 5 Then
            cell.Interior.Color = RGB(255, 0, 0) 'Red color for values greater than 5
        Else
            cell.Interior.Color = RGB(0, 255, 0) 'Green color for values less than or equal to 5
        End If
    Next cell
End Sub

Explanation:

The code defines a subroutine named ColorCellsBasedOnValue.
It declares a variable cell of type Range, which will be used to loop through each cell in column B.
The For Each loop iterates through each cell in the range Range("B1:B10").
The If statement checks the value of each cell. If the value is greater than 5, the background color of the cell is changed to red using the RGB function (255, 0, 0). Otherwise, if the value is less than or equal to 5, the background color is changed to green (0, 255, 0).

Example:

Sub SumColumnValues()
    Dim lastRow As Long
    Dim sumResult As Double

    'Find the last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    'Calculate the sum of values in column A
    sumResult = Application.WorksheetFunction.Sum(Range("A1:A" & lastRow))

    'Display the sum in a message box
    MsgBox "The sum of values in column A is: " & sumResult
End Sub

Explanation:

The code defines a subroutine named SumColumnValues.
It declares two variables lastRow and sumResult, where lastRow is used to store the last row with data in column A, and sumResult stores the sum of values in column A.
The Cells(Rows.Count, "A").End(xlUp).Row statement finds the last row with data in column A using the End method in combination with the xlUp direction.
The Application.WorksheetFunction.Sum function is used to calculate the sum of the range Range("A1:A" & lastRow), which contains all the values in column A from row 1 to the last row.
The calculated sum is then displayed in a message box using the MsgBox function.

Example:

Sub ApplyConditionalFormatting()
    Dim rng As Range
   
    'Specify the range where conditional formatting should be applied
    Set rng = Range("B2:B10")
   
    'Apply conditional formatting based on cell values
    With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="5", Formula2:="8")
        .Interior.Color = RGB(255, 255, 0) 'Yellow color
    End With
End Sub

Explanation:

The code defines a subroutine named ApplyConditionalFormatting.
It declares a variable rng to store the range of cells where conditional formatting should be applied.
The Set statement is used to specify the range B2 to B10 as the range where conditional formatting will be applied.
The With statement is used to work with the FormatConditions object of the specified range.
The Add method is used to add a new conditional formatting rule.
The conditional formatting is based on cell values between 5 and 8 (inclusive). If a cell value falls within this range, the formatting inside the With block will be applied.
The interior color of the cells is set to yellow using the RGB function.

Example:

Sub FillNumbersInCells()
    Dim i As Integer
   
    For i = 1 To 10
        Range("A" & i).Value = i
    Next i
End Sub

Explanation:

The code defines a subroutine named FillNumbersInCells.
It declares a variable i to be used as a loop counter.
The For loop is used to repeat the following actions from i = 1 to i = 10.
In each iteration of the loop, the Range("A" & i).Value statement sets the value of cell A1 to A10 to the value of i, which is the loop counter.

Example:

Sub CreateAndRenameSheet()
    Dim newSheet As Worksheet
   
    'Add a new worksheet and store the reference to the new sheet in the variable "newSheet"
    Set newSheet = ThisWorkbook.Sheets.Add
   
    'Rename the new worksheet to "NewSheet"
    newSheet.Name = "NewSheet"
End Sub

Explanation:

The code defines a subroutine named CreateAndRenameSheet.
It declares a variable newSheet to store the reference to the newly created worksheet.
The Sheets.Add method is used to add a new worksheet to the workbook, and the reference to the new sheet is assigned to the newSheet variable.
The Name property of the newSheet variable is set to "NewSheet" using the assignment statement (newSheet.Name = "NewSheet"), effectively renaming the newly created worksheet.