Macro to filter on every item in a list, then copy filtered range to another worksheet

[diygail123]

Hi, I wonder if anyone can help me with this, I have a list of vendor codes in column B on a worksheet called "Import", and I need to filter on every vendor code in this list, and copy the filtered range to another worksheet, the name of which will be the vendor code. Below is the code I have recorded, Vendor 000657 being the first vendor that appears when I click on the filter drop down. I can see that the vendor code is being hardcoded, which I dont want, as the vendor codes in the list will change month on month. I can also see that the filtered range is hardcoded, and this is no good either, as this range will change each month.

Can anyone help me? I am a beginner in VBA, but very keen to learn. I was hoping for some kind of loop that filtered on each different vendor code in column B?
Sub New_data()
'
' New_data Macro
'


'FILTER ON FIRST VENDOR
Sheets("Import").Select
ActiveSheet.Range("$A$1:$Q$5000").AutoFilter Field:=2, Criteria1:="000657"

'COPY AND PASTE THE FILTERED RANGE TO VENDOR TAB
Range("A431:F432").Copy
Sheets("657").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Import").Select
Range("H431:K432").Copy
Sheets("657").Select
Range("H4").Select
ActiveSheet.Paste

'FILTER ON SECOND VENDOR
Sheets("Import").Select
ActiveSheet.Range("$A$1:$Q$5000").AutoFilter Field:=2, Criteria1:="000661"

'COPY AND PASTE THE FILTERED RANGE TO VENDOR TAB
Range("A226:F232").Copy
Sheets("661").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Import").Select
Range("H226:K232").Copy
Sheets("661").Select
Range("H4").Select
ActiveSheet.Paste

 

[diygail123]

Hi, Recopied the code above and its working fine with F5 now, thanks so much for your help, my spreadsheet is now ready to roll out.

Very grateful

Gail

 

[Fluff]

Glad to help & thanks for the feedback