Automating Monthly Summary Sheets: Highlighting Duplicates Across Excel VBA Sheets
In many organizations, it is common to have a workbook that contains a sheet for every month's summary. In this article, we will explore how to automate the process of creating monthly summary sheets using Excel VBA and highlighting duplicates across sheets.
Setting up the Workbook
To begin, let's set up a workbook with 13 sheets, one for each month's summary. We will also create a summary page that contains two sets of tables: one for the current month and one for the previous month.
Sub CreateMonthlySummarySheets()
Dim ws As Worksheet
'Create a new worksheet for each month
For i = 1 To 12
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "Month " & i
Next i
'Create a summary page
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "Summary"
'Set up tables on the summary page
With ws.Range("A1")
.Value = "Current Month"
.Offset(1, 0).Value = "Previous Month"
.Offset(2, 0).Value = "Week 1"
.Offset(3, 0).Value = "Week 2"
.Offset(4, 0).Value = "Week 3"
.Offset(5, 0).Value = "Week 4"
End With
End Sub
Highlighting Duplicates Across Sheets
To highlight duplicates across sheets, we will use the RemoveDuplicates method of the Range object. This method removes all duplicate rows from a range, based on the columns specified. In this example, we will highlight duplicates based on the first column of each table.
Sub HighlightDuplicates()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
'Loop through each sheet
For Each ws In Worksheets
If ws.Name <> "Summary" Then
'Find the last row of the table
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'Set the range for the table
Set rng = ws.Range("A1:A" & lastRow)
'Remove duplicates
rng.RemoveDuplicates Columns:=1, Header:=xlYes
'Highlight duplicates
rng.SpecialCells(xlCellTypeConstants).Interior.Color = RGB(255, 255, 0)
End If
Next ws
End Sub
Running the Code
To run the code, simply press ALT + F11 to open the VBA editor, paste the code into a new module, and press F5 to run it. The code will create a new worksheet for each month's summary and set up tables on the summary page. It will also highlight any duplicates across sheets based on the first column of each table.
- In this article, we explored how to automate the process of creating monthly summary sheets using Excel VBA.
- We also learned how to highlight duplicates across sheets using the
RemoveDuplicatesmethod of theRangeobject. - By automating this process, we can save time and reduce the risk of errors.