Using VBA to print a dynamic range.

**j_Southern** · 10-22-2012, 11:31 AM

Dear All,
I am using the following code to select a defined dynamic range and to print it.
As written here it fails with an error message saying that the method is not supported.

Private Sub CommandButton1_Click()


   With Sheets("Envelope_List")
    ActiveSheet.PageSetup.PrintArea = Range("Envelope_List_Pivot")
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
   End With
End Sub

The code was arrived at by running a macro and changing the range details. If I run it as a macro it works.
If I change the code to:-

Private Sub CommandButton1_Click()
Macro1
End Sub

It fails again as above.
Where am I going wrong?
John

**shg** · 10-22-2012, 11:47 AM

The PrintArea property is a string, not a range, so

ActiveSheet.PageSetup.PrintArea = Range("Envelope_List_Pivot").Address

or just

Range("Envelope_List_Pivot").PrintOut

**j_Southern** · 10-22-2012, 12:12 PM

shg,
Thanks for your interest.
I have tried

With Sheets("Envelope_List")
ActiveSheet.PageSetup.PrintArea = Range("Envelope_List_Pivot").Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
   End With

and

   Range("Envelope_List_Pivot").Printout Copies: = 1

Both give Error "application or object defined error"
So I still don't quite understand.
John

**shg** · 10-22-2012, 04:01 PM

In your first example, you have a With statement that doesn't do anything. Activesheet (obviously) and Range refer to the active sheet, not to Worksheets("Envelope_List"). You might instead use

    With Worksheets("Envelope_List")
        .PageSetup.PrintArea = .Range("Envelope_List_Pivot").Address
        .PrintOut Copies:=1
    End With

I don't understand why the second example doesn't work for you. Is the range name valid?

**j_Southern** · 10-23-2012, 12:13 PM

shg
I am still in difficulty, with all your suggestions giving the application or object defined error. I am sure that the worksheet and range names are as stated. The range was defined as dynamic in the name manager using a formula like = Offset(Sheet1!($A$10,0,0,Counta(Sheet1!$A:$A),Counta(Sheet1!$5:$5)).
The code is driven by a button click event.
If you can think of anything else It would be very helpful.
regards
John