Loop to format all worksheets in workbook

Question

I've written a macro that formats a worksheet in preparation for export to pdf.

Option Explicit

Sub Formatting()

    Dim ws As Worksheet
    Dim cell As Range
    Dim rng1, rng2, rng3, rng4, ccode As Range
    Dim FinalRow As Long
    Dim chapter As String
    With ws
        'set ws = to sheet1
         Set ws = ActiveWorkbook.Sheets("Sheet1")

        'find the last row of data in column A
        FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        
        Set rng1 = Range("A3:A" & FinalRow)
    
        rng1.Unmerge
    
        Set rng2 = Range("B3:B" & FinalRow)
        For Each cell In rng2    'For each cell in the selected range, if the cell is empty, set the cell's value to be the same as the cell to the left.
            If cell.Value = "" Then
                cell.Font.Name = cell.Offset(0, -1).Font.Name
                cell.Font.Size = cell.Offset(0, -1).Font.Size
                cell.Font.FontStyle = cell.Offset(0, -1).Font.FontStyle
                Cells.VerticalAlignment = cell.Offset(0, -1).VerticalAlignment
                cell.Value = cell.Offset(0, -1).Value
            End If
        Next cell
             
        Set rng3 = Range("A:A")
        rng3.EntireColumn.Delete
        Set rng4 = Range("A:A")
        rng4.EntireRow.AutoFit
        
        chapter = Range("A" & FinalRow)
        Range("D3").Value = Mid(chapter, 44, 4)
        Range("D3").Font.Name = "Arial"
        Range("D3").Font.Size = "8"
        ws.Name = Range("D3")
    End With
     
End Sub

I need to loop through all sheets in the workbook before exporting them all.

I think the issue is here:

'set ws = to sheet1
Set ws = ActiveWorkbook.Sheets("Sheet1")

Since that needs to be dynamic as it loops through the sheets.

Answer 1

Something like this should work:

Sub Formatting()

    Dim ws As Worksheet, cell As Range, FinalRow As Long, chapter As String
    Dim c2 As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        FinalRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
        ws.Range("A3:A" & FinalRow).UnMerge
    
        'For each cell in the range, if the cell is empty, set
        '    the cell's value to be the same as the cell to the left.
        For Each cell In ws.Range("B3:B" & FinalRow).Cells
            With cell
                If .Value = "" Then
                    Set c2 = .offset(0, -1) 'cell to the left of `cell`
                    .Font.Name = c2.Font.Name
                    .Font.Size = c2.Font.Size
                    .Font.FontStyle = c2.Font.FontStyle
                    .VerticalAlignment = c2.VerticalAlignment
                    .Value = c2.Value
                End If
            End With
        Next cell
          
        ws.Range("A:A").EntireColumn.Delete
        ws.Range("A:A").EntireRow.AutoFit
        
        chapter = ws.Range("A" & FinalRow).Value
        With ws.Range("D3")
            .Value = Mid(chapter, 44, 4)
            .Font.Name = "Arial"
            .Font.Size = "8"
            ws.Name = .Value
        End With
    Next ws
End Sub