0

As an inventory control specialist, I set up a macro and some formulas to paste and mine data copied from a IBM Power 9 inventory bin location database. The end goal was to create a hard copy array of all bin locations with their bar codes and room for audit notes. The results worksheet is already set up with the desired formulas and formatting and divided into the desired print areas so that running the macro simply populates it with the names and barcodes of up to 2000 bin locations.

Everything works like a charm, but currently I need to use print preview and manually set the range of active sheets to print, as not all of the 48 pages spanning the active cells containing formatting and formulas are used for any given group of bins.

I have searched multiple sites for hours and have not found any code that is remotely helpful, and my self-taught VBA skill is limited to simple macros and slight modifications of other peoples' code. Is there a way to program a macro to print only pages (as defined by predetermined print areas) with values (non-""-result formulas) while ignoring pages filled only with ""-result formulas and formatting? I am running Excel for Office 365.

Thanks

David Blackburn
  • 17
  • 4
  • You may want to learn better search techniques. In less than three minutes of searching this site, I found [how to find formulas](https://stackoverflow.com/questions/19673635/check-if-there-are-any-formulas-in-worksheet), [how to get the result of a formula](https://stackoverflow.com/questions/28832054/vba-how-to-get-the-result-of-a-formula-value-always-returns-0), [how to set the print area](https://stackoverflow.com/questions/17623445/set-printing-area-in-excel-2013-using-macro), and [how to print specific pages](https://stackoverflow.com/questions/22661732/print-certain-pages-only). – ProfoundlyOblivious Sep 09 '19 at 03:03
  • I have the print areas set already, the whole document is full of formulas, the pages required are dynamic, not specific. I feel like I should be able to use the information in "how to get the result of a formula" But can't quite wrap my head around how to do so. Maybe I need to integrate the "return result of formula" concept into a macro so that it prints every page where the first line contains a non "" formula result, but I don't know how to reference pages as pre-defined by print areas, or how to set the macro to print only these pages. I am sadly lacking in experience here. – David Blackburn Sep 09 '19 at 03:38

2 Answers2

0

My response was too long for the comment field and, more or less, answers your question so I moved it here.

The way I imagine your data, I'd loop through every page in the workbook. While on a page, I'd loop through every formula until I found a result that is not blank. On finding a non-blank result, I'd add the page to a list of pages to print and move to the next page. After checking the last page, I'd print each page on the list.

You may want to define the steps required to complete your project (similar to what I just did) because each step requires several motions and almost every one of them will be a new challenge for someone with your experience. Once defined, find the most fundamental movements that are critical to your program and start there. Keep it simple, you will not always know what the challenge will be or where it will come from. What looked like a simple step, like looping between pages, requires a basic understanding of the workbook object and that is a far more complex subject than a loop.

Keep in mind that we are here to solve problems with your current code. If you don't have a problem with existing code then there is not much that can be done beyond pointing you toward a path.

ProfoundlyOblivious
  • 1,455
  • 1
  • 6
  • 12
  • Ok. Thanks for the general direction... I will try and piece something together and perhaps come back with something to work with. – David Blackburn Sep 09 '19 at 05:15
  • @DavidBlackburn Try not to discount the links I posted earlier, they are all relevant to your project. The 1st shows how to loop through cells and worksheets, the 2nd & 3rd how to reference a specific cell (as well as change print range if needed), and the 4th how to print specific pages. – ProfoundlyOblivious Sep 09 '19 at 05:46
  • Thanks for the help @ProfoundlyOblivious! While the links got me dizzy and lost trying to figure out VBA, they did get my brain jump started. Turns out the answer was far simpler than I thought and well within my skill level. All I needed were some simple formulas and a simple 1 line macro. Crazy. – David Blackburn Sep 09 '19 at 14:54
0

After reviewing the links provided by @ProfoundlyOblivious I quickly determined I lacked the skill to program a solution using VBA as suggested. However, trying to parse all that information got my brain on a roll and I did develop a very simple and sneaky workaround using formulas tied in with a simple one line macro. Assuming all pages predefined by print areas are the same width, and assuming there is at least one cell that will always consistently be populated in on a used page, these instructions should work for you should you have a similar issue.

Enter the following simple binary IF formula in a blank row of the first page:

=IF(A1<>"",1,0)

where A1 is a cell in the first page containing a formula that would be guaranteed to consistently return a value if that page is being used. (in my case, the imported information is imported in columns beginning with the upper left corner of each page).

Select the row of the page (not the entire row of the worksheet, just the width of the page as defined by the print area) containing the formula, and drag fill across to all possible pages, then in the same row of the last page simply add a SUM(,,,,) formula referencing (adding) all of the binary IF(X,1,0) formulas. The resulting sum will be the number of populated pages.

Clunky, but functional.

Finally, the simple print macro:

Sub Print_Audit_Sheets()
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=Range("JW42").Value, Copies:=1, Collate:=True, IgnorePrintAreas:=False
End Sub

Where ("JW42") is the reference to the range with the SUM() of all the binary IF() formulas. It isn't fancy, but it's simple, and simple works. Be sure to change the text color on the IF() binary and Sum() cells so that you don't have random 1s and 0s obtruding in your printouts.

You will need to modify this code if your first page is not consistently populated or your value-containing pages are not contiguous.

David Blackburn
  • 17
  • 4