0

I created a VBA script that will take information from SAP, extract it as an excel file, save it into a particular folder, and then prints out the sheets from the excel workbook that was created.

From the SAP data that is extracted, that data differentiates on the number of rows that are returned.

i.e.:

1st extraction: 5 rows are returned

2nd extraction: 15 rows returned.

3rd extraction: 10 rows returned. etc.

when these rows are extracted, they are then prepopulated into specific cells on multiple different sheets. If there are 5 rows, then that will only take up 3 sheets. 15 rows that will take up 10 sheets. However currently, the script is printing all the sheets even when they are empty.

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "V" Or sh.Name = "W" Or sh.Name = "X" Or sh.Name = "Y" Or sh.Name = "Z" Then  
sh.PrintOut Preview:=False, ActivePrinter:="Print&Go Americas", PrintToFile:=True, PrToFileName:=PSFileName 
End If
Next sh

As you can see, it is printing sheets VWXY and Z. Is there a way to NOT print unfilled cells.

i.e.

if cells A1, A2, and A3 is 0, then i can for that sheet to NOT print.

nosirrah
  • 31
  • 6
  • Have you tried [testing if those cells are empty](https://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba)? – BigBen May 19 '21 at 20:27
  • It sounds like you want to individually test each page worth of cells before printing. You could accomplish that with a loop and an If Statement. When True, Sheet.PrintOut one page at a time. – Toddleson May 19 '21 at 21:50

1 Answers1

0

If I read correctly, your question is "Can I modify Worksheets.PrintOut command in such a way that some cells don't get printed?".

The answer is: No, you can't. (As you can see in the official documentation, such a parameter does not exist)

However, what you can do, is create a new worksheet, copy all non-blank cells to that new sheet and print that new sheet. OR:
You can remove the blank cells from your worksheet and print the worksheet.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • can you provide the documentation on where i can go to to see where I can auto create a new worksheet each time when populating a table ? – nosirrah May 19 '21 at 23:56