How to copy Name

[YellowLabPro]

I am copying a range of data of one worksheet to a new workbook. I only want to copy the range and convert formulas to data. I am sure there are more clever ways... but this will get the job done for now.
However I need to copy over one NamedRange or create the same named range.
Could someone point out how to do this? The namedrange is "TotalRow" and is =FF!$A$133:$AG$133

[VBA]
Range("A1:AF133").Copy
Application.CutCopyMode = False
'Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("A:A").ColumnWidth = 2
Columns("B:E").EntireColumn.AutoFit
Columns("F:J").ColumnWidth = 11
Columns("K:AF").EntireColumn.AutoFit
Rows("1:6").RowHeight = 14.25
[/VBA]

Thanks,
YLP

[lucas]

I got this for you using the recorder yelp.
[VBA] ActiveWorkbook.Names.Add Name:="TotalRow", RefersToR1C1:= _
"=FF!R133C1:R133C33"[/VBA]

[YellowLabPro]

Steve,
When this adds the name, it points back to the FF sheet, yes?
If this is the case, I need to create its own Name and use the same cell reference but on it's own sheet.

Btw: How did you get the recorder to do that?

[lucas]

That's correct, I used the formula you provided:
[VBA]"=FF!R133C1:R133C33[/VBA]
so if it's to go into a different sheet in the new workbook you will have to change the FF part.

Btw: How did you get the recorder to do that?

I just started the recorder and went to insert name...put in the name "TotalRow" and then pasted the formula you provided using ctrl+V

[YellowLabPro]

Thanks Steve,
This makes good sense.

That's correct, I used the formula you provided:
[vba]"=FF!R133C1:R133C33[/vba]
so if it's to go into a different sheet in the new workbook you will have to change the FF part.

I just started the recorder and went to insert name...put in the name "TotalRow" and then pasted the formula you provided using ctrl+V

Thanks for the info.... albeit I am still not able to just stroll this just yet, things are starting to become clearer....

[YellowLabPro]

Ok....
That worked.....
[VBA]
ActiveWorkbook.Names.Add Name:="TotalRow", RefersToR1C1:="=Sheet1!R133C1:R133C33"
[/VBA]

Thanks Steve

[lucas]

Since your copying to a new workbook...would it be easier to just copy the whole sheet and then fix r up?

[YellowLabPro]

Not in this case, at least not in my opinion. If you think other wise let me know.
This sheet has 10 buttons and summary data below and to the right. My goal is to email this sheet off to my supplier w/out any traces of the supporting data or reference material. The only reason I wanted the "TotalRow" named range is to shrink up the unused lines on the order form.

It seemed to me a lot more difficult to have to remove all the other ranges and objects, (I am guessing the buttons would be objects), than just copying over what I wanted to give them. The last step on this guy now is to implement emailing it when it copies over to the sales rep and the company. Both email addresses are in the copied sheet. MDmackillop has put up info on this before so I just have to find this and put that in.

Again if you know of a way to do a massive delete of all but xxxx then I am all ears.

Thanks Steve,

Doug