by Allen Wyatt
(last updated May 25, 2019)
Mitchell has a lot of data in a worksheet that represents all of his company's purchase orders for a year. The data is sorted on column C, which contains the vendors' names. Mitchell wants to print a separate page for each vendor with all the data for those rows. He wonders if there is any way to automate the printing of vendor-specific sheets.
As with many things in Excel, there are multiple approaches you can take for this problem. I'm going to look at four approaches in this tip. All four approaches assume that your data is sorted according to the vendor-name column (column C) and that you have column heads on each column of your data (Name, Date, PO Number, Vendor, etc.).
To print vendor-specific sheets using subtotals, start by selecting a cell within your data. (A cell in column C would be perfect.) If your data is non-contiguous, you may need to select all of it manually; if it is contiguous, however, then selecting the single cell should be sufficient. Then, follow these steps:
Figure 1. Specifying how subtotals should be created.
Excel places subtotals in your worksheet, but it should also place page breaks before each new vendor. (This is because of step 7, above.) The page breaks may not be immediately obvious, but they come into play when you print the worksheet.
Once printed, what you end up with is a printed page for each of your vendors. The subtotal just below the last row on each page indicates the number of purchase orders printed for that particular vendor.
Using Filtered Data
Filtering your data is quite easy, and this is a good approach if you don't need to print these types of reports that often. Again, start by selecting a cell within your data, unless your data is non-contiguous. (In that case you'll need to manually select all your data.) Then, follow these steps:
If you want to print reports for other vendors, all you need to do is to change the filter (step 3) and reprint (step 4). When you are done, you can remove the filter by again clicking the Filter tool on the Data tab of the ribbon.
Another fast way to create the reports you want is to use the PivotTable capabilities of Excel. I won't go into how to create a PivotTable here, as that has been covered in other issues of ExcelTips. Your PivotTable can be set up just about any way you desire, but you need to make sure that the Vendor field is in the Filters group of the PivotTable Fields pane. (See Figure 2.)
Figure 2. Setting up your PivotTable.
Next, display either the Options or Analyze tab of the ribbon, depending on your version of Excel. (These tabs are only visible when you select a cell within your PivotTable.) In the PivotTable group, at the left of the ribbon, click the Options drop-down list and choose Show Report Filter Pages. (This option is available only if you made sure that the Vendor field is in the Filters group, as mentioned earlier.) Excel displays the Show Report Filter Pages dialog box. (See Figure 3.)
Figure 3. The Show Report Filter Pages dialog box.
There should be only a single field listed in the dialog box, unless you added more than the Vendor field to the Filters group. If there is more than one field listed, make sure you click on the Vendor field. When you click on OK, Excel creates separate PivotTable worksheets for each vendor in your data table. Depending on the information you chose to include in the PivotTable, these can make great reports for your vendors. You can then print the worksheets to get the reports you want.
There are many ways that you could set up a macro to give you the data you want. Personally, I prefer a macro that will go through your data and create new worksheets for each vendor. That's what the following macro does—it compiles a list of vendors from your data and then creates a worksheet named for each vendor. It then copies information from the original worksheet to the newly created worksheets.
Sub CreateVendorSheets() ' To use this macro, select the first cell in ' the column that contains the vendor names. Dim sTemp As String Dim sVendors(99) As String Dim iVendorCounts(99) As Integer Dim iVendors As Integer Dim rVendorRange As Range Dim c As Range Dim J As Integer Dim bFound As Boolean ' Find last row in the worksheet Set rVendorRange = ActiveSheet.Range(Selection, _ ActiveSheet.Cells(Selection.SpecialCells(xlCellTypeLastCell).Row, _ Selection.Column)) ' Collecting all the vendor names in use iVendors = 0 For Each c In rVendorRange bFound = False sTemp = Trim(c) If sTemp > "" Then For J = 1 To iVendors If sTemp = sVendors(J) Then bFound = True Next J If Not bFound Then iVendors = iVendors + 1 sVendors(iVendors) = sTemp iVendorCounts(iVendors) = 0 End If End If Next c ' Create worksheets For J = 1 To iVendors Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = sVendors(J) Next J ' Start copying information Application.ScreenUpdating = False For Each c In rVendorRange sTemp = Trim(c) If sTemp > "" Then For J = 1 To iVendors If sTemp = sVendors(J) Then iVendorCounts(J) = iVendorCounts(J) + 1 c.EntireRow.Copy Sheets(sVendors(J)). _ Cells(iVendorCounts(J), 1) End If Next J End If Next c Application.ScreenUpdating = True End Sub
As noted at the beginning of the macro, you should select the first cell of data in the Vendor column before running the macro. When completed, you'll have one worksheet for each vendor, which you can format and print as desired. (You could make the macro even more useful by adding code that will put column heading information or other information into each created worksheet.) When done, you'll need to delete the worksheets for those vendors so that the next time you run the macro you don't run into a problem.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13633) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
If your worksheet, when printed, requires more than a single page to print, you may want to only print a range of the ...Discover More
When you accumulate quite a few workbooks in a folder, you might need to print out selected worksheets from all of the ...Discover More
Adding a watermark to the background of a printout can be challenging. This tip explains the different ways you can ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.