PDA

View Full Version : How to copy Name



YellowLabPro
05-28-2007, 04:43 PM
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


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


Thanks,
YLP

lucas
05-28-2007, 04:57 PM
I got this for you using the recorder yelp.
ActiveWorkbook.Names.Add Name:="TotalRow", RefersToR1C1:= _
"=FF!R133C1:R133C33"

YellowLabPro
05-28-2007, 05:37 PM
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
05-28-2007, 08:07 PM
That's correct, I used the formula you provided:
"=FF!R133C1:R133C33
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
05-28-2007, 08:13 PM
Thanks Steve,
This makes good sense.

That's correct, I used the formula you provided:
"=FF!R133C1:R133C33
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....:clap:
:friends:

YellowLabPro
05-28-2007, 08:17 PM
Ok....
That worked.....

ActiveWorkbook.Names.Add Name:="TotalRow", RefersToR1C1:="=Sheet1!R133C1:R133C33"


Thanks Steve

lucas
05-28-2007, 08:19 PM
Since your copying to a new workbook...would it be easier to just copy the whole sheet and then fix r up?

YellowLabPro
05-29-2007, 04:41 AM
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