I am trying to loop through column A throughout all sheets in a workbook (in VBA). The task I am trying to accomplish is to list each unique string from column "a" on all worksheets in column "i" starting on i2 on the first worksheet.
I am trying to loop through column A throughout all sheets in a workbook (in VBA). The task I am trying to accomplish is to list each unique string from column "a" on all worksheets in column "i" starting on i2 on the first worksheet.
Hello,
Try this code, it worked for me.
I use the UNIQUE function that you could use in the worksheets directly instead VBA (it is up to you).
Sub uniques()
n = ActiveWorkbook.Sheets.Count
ScreenUpdating = False
For i = 1 To n
m = Application.WorksheetFunction.Unique(Sheets(i).Range("A:A"))
r = Application.WorksheetFunction.CountA(m)
Sheets(i).Activate
Sheets(i).Range(Cells(2, 9), Cells(1 + r, 9)) = m
Next
ScreenUpdating = True
End Sub