Order Template with vba to select distinct values and then loop through on those values in XL2007

mntrapper

New Member
Joined
Nov 7, 2014
Messages
2

<colgroup><col width="69"><col span="7" width="64"><col width="71"><col width="85"></colgroup><tbody>
</tbody>
Hi
I am hoping to learn how to setup a unique list of vendors (VendorList) from a list using a named range, then loop through that list in a VBA macro that is using filters. Each worksheet is named after the vendor and so is the table. So worksheet Vendor1 table Vendor1. My main sheet is called Orders and where we import all of the items to be ordered for the day. I know what vendors I have but not all will be ordered daily. I will add sheets to the workbook as we add vendors and I thought that this should be fairly simple but the simplicity has alluded me. We then do a print of each worksheet that had an order for the day. Thank you in advance.

So basically loop through unique list of vendors VendorList and do this code for each i in VendorList
Sheets("ORDERS").Select
ActiveSheet.ListObjects("Orders").Range.AutoFilter Field:=2, Criteria1:= _
"i"
Do While Range("Orders") <> ""
Range("Orders").Select
Selection.Copy
Sheets("i").Select
Range("i").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.PrintPreview

VendorName
ProductID
Vendor1Vendor18969-01
Vendor1Vendor18969-02
Vendor1Vendor18969-03
Vendor1Vendor18969-04
Vendor2Vendor28969-04
Vendor2Vendor28969-05
Vendor2Vendor28969-06
Vendor2Vendor28969-07
Vendor2Vendor28969-08
Vendor2Vendor28969-15
Vendor3Vendor38969-15
Vendor3Vendor38969-16
Vendor3Vendor38969-16
Vendor3Vendor38969-16

<colgroup><col><col></colgroup><tbody>
</tbody>
 

mntrapper

New Member
Joined
Nov 7, 2014
Messages
2
I found this which is similar to what I want to do but I am using a table so how does that affect the code? Any help is appreciated!!!
Sub AutoLoop()
Dim c As Range
Dim rng As Range
Dim LR As Long

LR = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("A1:AJ" & LR)

Range("E1:E" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True

For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))
With rng
.AutoFilter
.AutoFilter Field:=5, Criteria1:=c.Value
.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
ActiveSheet.Paste
End With
Next c

End Sub
 

Forum statistics

Threads
1,082,509
Messages
5,365,982
Members
400,864
Latest member
alogia67

Some videos you may like

This Week's Hot Topics

Top