Copy and paste a named range

Hi again Ezybao

You didn't attach a file to your original Question (or attempted to attach Lotus 123 file- but this Forum only allows Excel files to be uploaded).

Also your Forum Profile doesn't say which version of Excel you are using.

It's easy enough to copied a named range, paste it and rename in a few clicks (without VBA)...

In the FIRST attached file I've tried to recreate what you describe, with named range RAMCAP in yellow cells B1:G1 and  US, K and BAZ in coloured cells A2, A4 and A6 respectively. Note that C2 is blue since I changed the formula to multiply it by C1 (which I set to 45), so (changes in bold):

=C1*45*1/K/US*BAZ

To copy the range RAMCAP:

1. click in any single cell then click the down arrow where the cell name (e.g. B5)  appears (to the left the formula bar normally)
2. Click on RAMCAP in the list of named ranges (the selection will change to B1:G1)
3. press Ctrl+C to copy the range (dotted lines will appear around it)
4. click in the desired cell (say B5) and press Ctrl+V- the cells will be copied including any formula
5. without changing the selection, go ribbon Formulas then click Name Manager (under the Defined Names section)
6. in the Name Manager requestor, click New... then in the next requestor, name the new range under Name: and click OK

The formulae are copied relatively (so C1 will be C5- so chnage that and D5 will alter to suit.

It's probably easier to learn this simple method unless you have to do this many times- when it might warrant your (/my spare) time to write the VBA code to cover it.

Revision 08 January 2023

From the Discussion below, I now understand you have hundreds or thousands of Named ranges to paste into quotations.

In the SECOND attached file, you'll see a spreadsheet with three sheets: Quotation, Database and Variables (the latter just storing formula variables like K, US and BAZ). I made up some (coloured) Named ranges in the sheet Database, listing their names in column A (take a look). 

If yoiu open the file (with macros enabled), go to the Quotations sheet, selct a cell in column B (say) and press Ctrl+j - this will caused the code behind that sheet to run:

Sub PasteCode()
    '
    ' PasteCode Macro
    '
    ' Keyboard Shortcut: Ctrl+j
    '
    ' display the form
    UserForm1.Show
End Sub

since it is "assigned" to the key combination Ctrl plus j (you can change that).

That brings up UserForm1 which runs this code and gets puts the all the named ranges from the worksheet Database into an array called Cds - for Codes. I've added comments so you see what is happening:

Private Sub UserForm_Activate()

    ' size the array to hold all names
    ReDim Cds(ActiveWorkbook.Names.Count)

    p = 1
    ' loop through the Names
    For n = 1 To ActiveWorkbook.Names.Count
        ' look at the nth Name
        CdNm = ActiveWorkbook.Names(n).Name
          ' if the range is on the Database sheet...
        CdRng = ActiveWorkbook.Names(n).RefersTo
        If InStr(1, CdRng, "Database", vbTextCompare) > 0 Then
          ' add the Name to the array
          Cds(p) = CdNm
          p = p + 1
        End If
    Next n

    ' resize the array to remove any unused locations
    ReDim Preserve Cds(p - 1)
    ' put cursor in textbox, ready for typing
    TextBox1.SetFocus
    ' allow events to occur
    Me.DisableUFevents = False
End Sub

That uses some varaibles which are declared at the UserForm level (at the top) so they keep their contents by these lines:

Public DisableUFevents As Boolean, Cds As Variant
Public CdNm As String, CdRng As String, n As Long, p As Long
Option Base 1
Option Explicit

As you type into the text box, it looks for an exact match between the contents and what's in the array Cds:

Private Sub TextBox1_Change()
     ' do nothing if change arises from code
    If DisableUFevents = True Then Exit Sub
    ' set the form to grey and label to nothing
    UserForm1.BackColor = -2147483633
    Label1.Caption = ""
    Label1.BackColor = -2147483633

    ' loop through the Names in the array
    For n = 1 To UBound(Cds)
        If TextBox1.Text = Cds(n) Then
                ' if text is a valid code, turn form green and advise user
                UserForm1.BackColor = 8454016
                Label1.Caption = "Found! Press Enter to paste"
                Label1.BackColor = 8454016
                Exit For
            End If
    Next n
End Sub

If you type a code which matches a Named range in Database, you'll see the form turns green (to indicate a match) and suggests you press Enter. If you do, that's detected by this code:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ' do nothing unless the Enter button was pressed
    If KeyCode <> vbKeyReturn Then Exit Sub
    ' do nothing if no entry in ComboBox1
    If TextBox1.Text = "" Then Exit Sub
    ' if entry exists...

    ' loop through the Names in the array to check entry is a code
    For n = 1 To UBound(Cds)
        If TextBox1.Text = Cds(n) Then
                ' if text is a valid code, paste and advise user
                Range(Cds(n)).Copy Selection.Resize(1, 1)
                ' ... prevent prevent Textbox change re-triggering by actions below
                Me.DisableUFevents = True
                ' set the form to grey and text + label to nothing
                UserForm1.BackColor = -2147483633
                TextBox1.Text = ""
                Label1.BackColor = -2147483633
                Label1.Caption = Cds(n) & " was pasted to " & Selection.Address(0, 0)
                Me.DisableUFevents = False
                ' move down a cell (for the next code entry)
                Selection.Offset(1, 0).Select
                Exit For
            End If
    Next n

End Sub

which again checks that the code exists and then paste the range into your cell (or starting from the top leftmost cell if you had several selected) and tells you.

It moves the cursor to the next cell down and you can enter another code or press the x (top right of the form to stop.

It might seem like a lot of fairly sophisticated code just to replicate what Lotus123 did but I hope this allows you to work efficiently. You could have thousands of Named ranges in that Database sheet and it will still work quickly (since I used an array for the comparison).

Hope this solves your problem. If so, please remember to mark this Answer as Selected (or do that to another Answer if you get one and prefer that)