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
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:
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:$A$4:$H$26
Looking through the generated VBA, we see only one significant line:
So now all we need to do is move that line to your the procedure where you need it and make a small modification:
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:
(Do not "ignore print areas".)
All together now: