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...??