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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Looping through Column A and repeating through all worksheets
0
Answers
0
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