Set Print Area

[djblois1]

I am using code to select a Print Area and it keeps only selection one cell and giving me an error. Here is the code:

Cells.Find(What:="TX", After:=Cells(finalrow, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 18).Select
    
    Range("A2", ActiveCell).Select
        
    ActiveSheet.PageSetup.PrintArea = Selection

I have tried many other ways and they all keep giving me the same problem. Thank you all for the help in advance.

[shg]

What are you trying to set the print area to?

[djblois1]

from range A2 to the cell that it finds in the find command. It will always be a different spot so I have to make it dynamic like I did.

[shg]

Try this:

    Dim r As Range
    
    Set r = Cells.Find(What:="TX", _
               After:=Cells(Rows.Count, "A"), _
               LookIn:=xlValues, _
               LookAt:=xlPart, _
               SearchOrder:=xlByColumns, _
               SearchDirection:=xlPrevious, _
               MatchCase:=True)
    If r Is Nothing Then
        MsgBox "Not found"
    Else
        ActiveSheet.PageSetup.PrintArea = Range("A2", r.Offset(, 18)).Address
    End If

[djblois1]

Thank you that worked perfectly.

How do you know a way to set the amount of copies? I want the print dialog to open with 12 copies already filled in?

[shg]

Try recording a macro, and we'll help you clean it up.

[djblois1]

I just did record a macro and this is what comes out:

ActiveWindow.SelectedSheets.PrintOut Copies:=12

That is not what I want because it does not bring up the print dialog box. I want to set the copies to 12 but bring up the print dialog box. Is this possible?

[shg]

Application.Dialogs(xlDialogPrint).Show

[djblois1]

I already have that code. The issue is I want it to open with 12 copies already set. Is that possible?

[shg]

Don't think so.

[MarvinP]

Maybe you should do a .printpreview and change it to 12 by hand and then have it print.