by Allen Wyatt
(last updated May 2, 2015)
Madhabi wants to print four copies of a worksheet, but have the value of cell A7 change for each copy. The four values are "Original," "Duplicate," "File," and "Driver." All other data being the same, Madhabi wonders how he can change the text of cell A7 while printing.
One possible approach is to create four different worksheets that contain essentially the same information. Worksheets 2 through 4 could all reference cells on worksheet 1, with the exception of cell A7 which could be set on each worksheet to a different value ("Original," "Duplicate," etc.).
A better approach, however, would be to create a quick macro that you could use for your printing. The macro could handle changing the value of cell A7 just before the worksheet is printed each time. Here's a simple example:
Sub PrintCopies() Dim i As Integer Dim VList As Variant VList = Array("Original", "Duplicate", "File", "Driver") For i = LBound(VList) To UBound(VList) Range("A7") = VList(i) ActiveSheet.PrintOut Next End Sub
The macro places the four values destined for cell A7 into an array. The macro then steps through each of the four array elements, putting the value into cell A7, and then printing the worksheet. Printing is done to whatever the default printer is on the system.
The macro could easily be assigned to a shortcut key or to the Quick Access Toolbar (as described in other ExcelTips) so that it you could print all four copies quickly.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9962) applies to Microsoft Excel 2007, 2010, and 2013.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
In a large worksheet, you may want to display and print just a portion of the available data. Displaying the desired ...Discover More
If you need to modify where a worksheet is printed (meaning, which paper tray it should use), Excel doesn't provide a lot of ...Discover More
This tip presents two techniques you can use to print multiple workbooks all at the same time. Both techniques involve ...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.