VBA Code for Data Advanced Filer Unique Records

mazher

Active Member
Joined
Nov 26, 2003
Messages
314
Dear Forum Members,

I need some help from a VBA expert.

I have a summary sheet with the sheet names in Column B starting B2 and down

I have the seller names in each sheet in column B starting B2

I need a VBA code that can check the column B of every sheet mentioned in the Summary sheet and paste me a list of the Unique Suppliers starting in Cell R3 downwards of each sheet

Presently I am using Data Adbaced Filter and have to do it manually for each sheet, by the sheet are increasing after few days and is becoming very tedious.

Hope some VBA expert can help me with this

I have used the macro recorder and it gave me this code

VBA Code:
Range("D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("D2:D111").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "R2"), Unique:=True

Thanks in advance
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,608
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub mazher()
   Dim Cl As Range
   
   With Sheets("Summary")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets(Cl.Value).Range("D2:D1000000").AdvancedFilter xlFilterCopy, , Sheets(Cl.Value).Range("R2"), 1
         End If
      Next Cl
   End With
End Sub
 

mazher

Active Member
Joined
Nov 26, 2003
Messages
314
Thanks for your reply @Fluff

I have inserted the code in the module and when I ran the macro it's giving me an error

Runtime Error '1004'

The extract range has a missing or an invalid field name

On pressing debug it takes me to this line of code

VBA Code:
Sheets(Cl.Value).Range("D2:D1000000").AdvancedFilter xlFilterCopy, , Sheets(Cl.Value).Range("R2"), 1
 

mazher

Active Member
Joined
Nov 26, 2003
Messages
314
Dear @Fluff
I spotted the problem, the extracted range has data in it.

Please can some code be added to clear the extracted range before doing the Data>Advanced Filter > Unique Records?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,608
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you already have data in R2?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,608
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub mazher()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Summary")
   For Each Cl In Ws.Range("B2", Ws.Range("B" & Rows.Count).End(xlUp))
      If Evaluate("isref('" & Cl.Value & "'!A1)") Then
         With Sheets(Cl.Value)
            .Range("R2", .Range("R" & Rows.Count).End(xlUp)).ClearContents
            .Range("D2", .Range("D" & Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("R2"), 1
         End With
      End If
   Next Cl
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,608
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,560
Messages
5,741,830
Members
423,689
Latest member
Jords998

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top