Consulting

Results 1 to 8 of 8

Thread: How to copy Name

  1. #1

    How to copy Name

    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
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  2. #2
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I got this for you using the recorder yelp.
    [VBA] ActiveWorkbook.Names.Add Name:="TotalRow", RefersToR1C1:= _
    "=FF!R133C1:R133C33"[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    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?
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  4. #4
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Thanks Steve,
    This makes good sense.
    Quote Originally Posted by 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.

    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....
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

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

    Thanks Steve
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  7. #7
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Since your copying to a new workbook...would it be easier to just copy the whole sheet and then fix r up?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    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
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •