VBA Code for Dynamic Print Area where rows = 10 + "$G$10"

Question

I need vba code for to set the print area of Customer Receipt worksheet to include columns A thru H, starting at $A$4 and extending down for a number of rows that is equal to 10 + the value in $G$10. For example, if the value in $G$10 is 16, the resulting print area is $A$4:$H$26. I have tried using formulas in the Name Manager, all of which have resulted in printing the entire worksheet as though no print area was set (standard Set Print Area works, but not what I need). FYI, I already have the following macro in place for this worksheet:

Sub PrintCustomerReceipt()
' PrintCustomerReceipt Macro
' Macro to print 2 copies of Customer Receipt on designated printer
' Keyboard Shortcut: Ctrl+Shift+Q

    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Answer 1

Your question is a little unclear; I think you're over-complicating a simple task. Based on the example in your first two sentences, this is the line that you need:

ActiveSheet.PageSetup.PrintArea = "$A$4:$H$" & (10 + Range("G10"))

---

When I need to run a built-in task, as-is or modified, (in this case Set Print Area), I let Excel "write the first draft" of the code for me:

1. Developer menu > Record Macro > take note the macro name (likely Macro1) > OK
2. Select (highlight) cells $A$4:$H$26
3. Page Layout menu > Print Area > Set Print Area
4. Developer menu > Stop Recording > Macros
5. Click the name of the macro from Step 1 > Edit

Looking through the generated VBA, we see only one significant line:

ActiveSheet.PageSetup.PrintArea = "$A$4:$H$26"

So now all we need to do is move that line to your the procedure where you need it and make a small modification:

ActiveSheet.PageSetup.PrintArea = "$A$4:$H$" & (10 + Range("G10"))

Any time this line is executed the print area for the worksheet is "dynamically" set based on the value in G10.

To print, just use the standard print command with default options, or if you want 2 copies:

ActiveWindow.SelectedSheets.PrintOut Copies:=2

(Do not "ignore print areas".)

---

All together now:

Sub PrintCustomerReceipt() 
    ActiveSheet.PageSetup.PrintArea = "$A$4:$H$" & (10 + Range("G10"))
    ActiveWindow.SelectedSheets.PrintOut Copies:=2
End Sub