I have this code so far, only does it for sheet 2, how can I alter this code to include multiple sheets into this? Complete newb here. :
Sub extractuniquevalues()
Sheet1.Range("C:C").AdvancedFilter xlFilterCopy, , Sheet4.Range("C1"), True
Sheet2.Range("C:C").AdvancedFilter xlFilterCopy, , Sheet4.Range("C1"), True
End Sub
You can do it like that:
Filter data in place:
Sub extractuniquevalues()
Dim wks As Excel.Worksheet
For Each wks In Excel.ActiveWorkbook.Worksheets
Call wks.Range("C:C").AdvancedFilter(XlFilterAction.xlFilterInPlace, , , True)
Next wks
End Sub
Filter data and paste them into a new worksheet:
Sub extractuniquevalues2()
Dim wks As Excel.Worksheet
Dim wksSummary As Excel.Worksheet
'----------------------------------------------------------------------------------
On Error Resume Next
Set wksSummary = Excel.ThisWorkbook.Worksheets("Unique data")
On Error GoTo 0
If wksSummary Is Nothing Then
Set wksSummary = Excel.ThisWorkbook.Worksheets.Add
wksSummary.Name = "Unique data"
End If
'Iterate through all the worksheets, but skip [Summary] worksheet.
For Each wks In Excel.ActiveWorkbook.Worksheets
With wksSummary
If wks.Name <> .Name Then
If Application.WorksheetFunction.CountA(wks.Range("C:C")) Then
Call wks.Range("C:C").AdvancedFilter(xlFilterCopy, , .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1), True)
End If
End If
End With
Next wks
End Sub
Unique data from each worksheet are printed in the first column of a new worksheet called Unique data.
This method filters data from each worksheet separately, so if there is for example value A in Sheet1 and value A in Sheet2, there will be two entries A in the result list.
Note that first value is considered to be a header and it can be duplicated in the result list.
I feel your comments warrant me posting this as an answer so that I may be a bit more thorough. This is meant only to add to the answer provided by mielk!
The object hierarchy in excel is roughly summarized by "An Excel Application owns workbooks. An Excel Workbook owns Worksheets. An Excel Worksheet owns Ranges." For more info on that look here.
When you click on an excel file to open it you are effectively doing 2 things:
When you open up subsequent Excel files, Excel will skip step one and simply open a workbook in the Excel Application that is already running. Note this means that similar to how a Workbook can have many Worksheets a single Excel Application can have multiple Workbooks that belong to it.
There are multiple ways to access these workbooks in VBA. One way is to use the application's Workbooks member much like you used a Workbook's Sheets member to access worksheets. Often though you simply want to access the Workbook that the user is currently editing/working on. To do this you can use ActiveWorkbook which is automatically updated for you whenever the user begins work on a different workbook.
Another Workbook you will often want to use is the workbook that "houses" the code you are running. You can do this by using ThisWorkbook. If you open up the VBA editor and look at the project viewer, you can even see a reference to ThisWorkbook! If you want your code to only update/alter the workbook that contains it then ThisWorkbook is the way to go.
As an example:
Let's say you have a macro to loop through all of the open workbooks and put the number of sheets each Workbook "owns" into some Worksheet in the "master" workbook.
You could do something like this:
Sub CountThem()
Dim wb As Workbook
Dim outputCell As Range
Dim nextRow As Integer
nextRow = 1
For Each wb In Application.Workbooks
wb.Activate
ThisWorkbook.Sheets("MySheet").Cells(nextRow, 1).Value = ActiveWorkbook.Sheets.Count
nextRow = nextRow + 1
Next
End Sub
You would put this code as a module in the "Master" workbook.
Let me know if this clears things up for you! :)
