Macro to create a varying named range

[jwilkes]

What I want to do is have a macro that selects all cells containing
data, creates a range and names it "Print_Area" sounds simple, but I
cannot get it to work

ActiveSheet.UsedRange.Select
ActiveWorkbook.Names.Add Name:="Print_Area", RefersToR1C1:= _
"=R396070CS01!R1C1:R38C17"

Ive got that, which works fine, however, i need to to be run in many
spreadsheets, with varying sheet names, and varying cell ranges.

Ideas...??

 

[reklamo]

Try following:

ActiveSheet.UsedRange.Select
Selection.Name = "Print_Area"

regards
reklamo

 

[Dave Peterson]

Won't clearing the print area let excel just use the usedrange?

Dim Wks as worksheet
for each wks in activeworkbook.worksheets
wks.PageSetup.PrintArea = ""
next wks

But if you want...

Dim Wks as worksheet
for each wks in activeworkbook.worksheets
wks.usedrange.name = "'" & wks.name & "'!Print_Area"
next wks