+ Reply to Thread
Results 1 to 3 of 3

possible to copy cell with cell range name to worksheet??

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    possible to copy cell with cell range name to worksheet??

    Hi all, not sure if this is possible, i have been searching for info but have come up empty handed! i have have one worksheet with cells and named ranges. i have successfully entered the code to select the range and copy it to another worksheet. IE:
    Sheets("Sheet2").Range("copyrng").copy
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Range("A65535").End(xlUp).Select
    Selection.PasteSpecial xlPasteAll
    However...in the range, copyrng, that I am copying, there are cells with named ranges. the cells copy but the named ranges do not! I need those named ranges to copy to the new worksheet! Not sure if this is even possible but any help is appreciated.
    Thanks

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: possible to copy cell with cell range name to worksheet??

    Public Function copy_named_range(copy_range As Range, dest_range As Range)
    Dim ws_copy, ws_dest, wb_copy, wb_dest, nm, offset_row, offset_col
    Set ws_copy = copy_range.Parent
    Set ws_dest = dest_range.Parent
    Set wb_copy = ws_copy.Parent
    Set wb_dest = ws_dest.Parent
    offset_row = dest_range.Cells(1).Row - copy_range.Cells(1).Row
    offset_col = dest_range.Cells(1).Column - copy_range.Cells(1).Column
    copy_range.Copy dest_range
    For Each nm In wb_copy.Names
        If Not Intersect(nm.RefersToRange, copy_range) Is Nothing Then
            wb_dest.Names.Add Name:=nm.Name, RefersTo:=ws_dest.Range(nm.RefersToRange.Offset( _
                offset_row, offset_col).Address)
        End If
    Next
    End Function
    Use like:
    sub macro_1()
    copy_named_range Sheet1.Range("A1:A10"), Sheet2.Range("C1:C10")
    end sub

  3. #3
    Forum Contributor
    Join Date
    09-12-2013
    Location
    chicago, illinois
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: possible to copy cell with cell range name to worksheet??

    phew!! thank you!!! that was way over my head but a great learning experience.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Match Cell Range to Worksheet Name and copy/paste to respective worksheet
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2014, 04:06 PM
  2. RE:- Write Macro to copy a 2 cell range to another 2 cell range down the columns
    By Eire001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2013, 11:48 PM
  3. Vba code to copy and paste second visible cell to other worksheet range then the next
    By Justin25150 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2012, 09:03 AM
  4. Copy worksheet and rename based on cell range
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-08-2011, 06:53 AM
  5. Replies: 0
    Last Post: 10-11-2010, 11:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1