Copy user defined range from another workbook

[Misca]

I hope I can find some help to my vba problem here:

I'm trying to get my macro to copy the values from an user defined range in one workbook to another workbook (the destination workbook is where the macro is located at). How can I do this?

Also, the seleceted range will be 57 rows tall and either 21 or 22 column wide and if there are only 21 columns in the selection, the macro should double the column 17 (=the pasted size would always be 57 rows by 22 columns).

Thank you already in advance!

 

[RolfJ]

You should be able to copy named ranges from one workbook to another using this macro when placed in the destination workbook:

Option Explicit
Sub CopyNamedRanges()
    Dim sourceWb As Workbook
    Set sourceWb = GetObject({full path name of source workbook})
    Dim namedRange As Name
    On Error Resume Next
    Dim nNames As Long
    For Each namedRange In sourceWb.Names
        Debug.Print namedRange.Name & ": " & namedRange.RefersTo
        With namedRange
            nNames = ActiveWorkbook.Names.Count
            ActiveWorkbook.Names.Add _
                Name:=.Name, _
                RefersTo:=.RefersTo, _
                Visible:=.Visible, _
                MacroType:=.MacroType, _
                ShortcutKey:=.ShortcutKey, _
                Category:=.Category, _
                NameLocal:=.NameLocal, _
                RefersToLocal:=.RefersToLocal, _
                CategoryLocal:=.CategoryLocal, _
                RefersToR1C1:=.RefersToR1C1
                'ShortcutKey creates an error if not assigned
            If nNames = ActiveWorkbook.Names.Count Then
                'execute the next statements only if previous add statement not successful
                ActiveWorkbook.Names.Add _
                    Name:=.Name, _
                    RefersTo:=.RefersTo, _
                    Visible:=.Visible, _
                    MacroType:=.MacroType, _
                    Category:=.Category, _
                    NameLocal:=.NameLocal, _
                    RefersToLocal:=.RefersToLocal, _
                    CategoryLocal:=.CategoryLocal, _
                    RefersToR1C1:=.RefersToR1C1
                    'Category creates an error if not assigned
                If nNames = ActiveWorkbook.Names.Count Then
                    'execute the next statements only if previous add statement not successful
                    ActiveWorkbook.Names.Add _
                        Name:=.Name, _
                        RefersTo:=.RefersTo, _
                        Visible:=.Visible, _
                        MacroType:=.MacroType, _
                        NameLocal:=.NameLocal, _
                        RefersToLocal:=.RefersToLocal, _
                        CategoryLocal:=.CategoryLocal, _
                        RefersToR1C1:=.RefersToR1C1
                        'CategoryLocal creates an error if not assigned
                    If nNames = ActiveWorkbook.Names.Count Then
                        'execute the next statements only if previous add statement not successful
                        ActiveWorkbook.Names.Add _
                            Name:=.Name, _
                            RefersTo:=.RefersTo, _
                            Visible:=.Visible, _
                            MacroType:=.MacroType, _
                            NameLocal:=.NameLocal, _
                            RefersToLocal:=.RefersToLocal, _
                            RefersToR1C1:=.RefersToR1C1
                    End If
                End If
            End If
        End With
    Next namedRange
    sourceWb.Close
    On Error GoTo 0
End Sub

The On Error Resume Next statement and the checking whether the previous Add statement was successful, and the series of Add statements with an increasingly smaller number of options seems to be required, because referring to several of the Name properties causes an error when the property has not been assigned.

Hope this helped,
Rolf

PS:
I have no suggestion for your question about the dynamic column width adjustment, because I don't understand what you mean by 'the seleceted range will be 57 rows tall and either 21 or 22 column wide... '.