Set print area based on a cell value with vba

    I am inexperienced with vba and have been using an if formula code to set print area on some tags on a worksheet. It has worked fine except I now need to drastically expand the number of tags. Is there another option I could use? Offset? Example follows and continues through the tags.


    Code
    If  Range (A23) . Value>0 Then
ActiveSheet.PageSetup.PrintArea=A1:K25
If  Range (A48) . Value>0 Then
ActiveSheet.PageSetup.PrintArea=A26:K50


    Thank you for any time you can give me.
    Mike Jaco

    Re: Set print area based on a cell value with vba


    Is there a defined area that will contain the flag?


    What could be done is monitor that area of the worksheet for changes. When a change is made, all other cells are set to 0 and then the print area set accordingly...


    I notice you started and ended your code with a square bracket - think you were trying to add code tags.


    The proper syntax is
    [noparse]

    Code
    [/noparse][/b]   '// Your code here[b][noparse]

    [/noparse][/b]


    Your thread has been edited to correct.

    Re: Set print area based on a cell value with vba


    Hi, mikejaco,


    some parts of your code must have gone while copying.


    I can´t figure out a reason to start at the top for the loop (I´d preferred to start from the bottom up as the last will overwrite any existing pagesetup). Code may be applied to the Workbook_BeforePrint(Cancel As Boolean) event in ThisWorkbook and may be altered to reflect a certain sheet or find out the last used row on that sheet:


    Code
    Dim lngCounter As Long
For lngCounter = 0 To 10
  If Cells(23 + lngCounter * 25, "A").Value > 0 Then
      ActiveSheet.PageSetup.PrintArea = Range(Cells(1 + lngCounter * 25, "A"), Cells((lngCounter + 1) * 25, "K")).Address
  End If
Next lngCounter


    Code
    Dim lngCounter As Long
For lngCounter = 23 To 223 Step 25
  If Cells(lngCounter, "A").Value > 0 Then
      ActiveSheet.PageSetup.PrintArea = Range(Cells(lngCounter - 22, "A"), Cells(lngCounter + 2, "K")).Address
  End If
Next lngCounter


    Ciao,
    Holger

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login