asked on

Copy named range into another worksheet and link the values

Dear Experts:

I would like to achieve the following using VBA:

Copy the named range (workbook level name = MyRange) of the worksheet named 'Data' to the worksheet named 'Results' with the following requirements...

.... Link the values
.... Start the pasting action on cell reference A5 of the 'Results' worksheet.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

andrewssd3

One way of doing this is to enter and array formula referencing the name in the Results sheet, as follows:

Sub CopyRange()
    
    Dim wbk As Excel.Workbook
    Dim rngSource As Excel.Range
    Dim rngTarget As Excel.Range
    
    Set wbk = ActiveWorkbook

    Set rngSource = Application.Range("MyRange")
    Set rngTarget = wbk.Worksheets("Results").Cells(1, 5)
    Set rngTarget = rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
    
    rngTarget.FormulaArray = "=MyRange"

End Sub

Open in new window

Andreas Hermle

ASKER

Hi andrew:

thank you very much for your professional support. This macro works great and will come in handy for me in other applications.

But regrettably I will have to move some parts of the copied array to other locations in the destination sheet and this results in the 'error' message:

'you cannot change part of an array'

So I would be pretty happy if you or somebody else could provide me with another solution to my requirements.

But again, thank you very much for your great support. Your approach is a neat one.

Regards, Andreas

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Hi andrew:

works like a charm. Thank you very much for your professional help. Regards, Andreas