Hello everyone,

So like the titles says, I'm trying to create a new worksheet based on duplicates in multiple worksheets. I am halfway there already with the following code; however, it is only looking at Sheet1 and Sheet2. What I am trying to do, is have multiple worksheets, maybe up to 15 different worksheets, to locate duplicate ROWS and transfer those ROWS to a new worksheet. The following code allows me to search for duplicates in sheet1 and sheet2, but I am unable to figure out how to allow VBA to search thru multiple worksheets. One day I might have 8, another day I might have 15, but I would like to figure out how to adjust this code so it won't matter the amount of worksheets I have, it will search thru them all, locate the duplicate ROWS and paste them or place them into a new RENAMED worksheets. See VBA code below on how I started out already. Can anyone inform me on what I can add or adjust to create my desired results?

Sub Report()
Dim ar As Variant
Dim i As Long
Dim j As Long
Dim n As Long

ar = Sheet2.Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(ar, 1)
.Item(ar(i, 1)) = Empty
Next
ar = Sheet1.Cells(1).CurrentRegion.Value
n = 1
For i = 2 To UBound(ar, 1)
If .exists(ar(i, 1)) Then
n = n + 1
For j = 1 To UBound(ar, 2)
ar(n, j) = ar(i, j)
Next
End If
Next
End With
Sheets.Add().Cells(1).Resize(n, UBound(ar, 2)).Value = ar
ActiveSheet.Name = "Report"
End Sub