Sending data to Excel 1

[TrekBiker]

I'm exporting data to an Excel spreadsheet but getting an error setting the PrintArea.

    With objSht.PageSetup
        .Orientation = xlLandscape
'        .PrintArea = "$A$1:$M$92"
        .PrintArea = .UsedRange.Address
        
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 99
    End With

The commented line .PrintArea = "$A$1:$M$92" works but I want the code to automatically use the spreadsheet range to make it more versatile.
A search says I can use UsedRange but with this line get Error 438, 'Object doesn't support this property or method'

        .PrintArea = .UsedRange.Address

Any help appreciated.

 

[combo]

UsedRange is a property of Worksheet object, so either:
[tt].PrintArea = .Parent.UsedRange.Address[/tt]
or
[tt].PrintArea = objSht.UsedRange.Address[/tt]


combo

 

[TrekBiker]

Excellent, thanks, that fixed it.