Results 1 to 2 of 2

referencing a named range in an external worksheet

This is a discussion on referencing a named range in an external worksheet within the Excel Questions forums, part of the Question Forums category; Hi Guys. Hope this is an easy one. In my spreadsheet i want to create a macro that will copy ...

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    98

    Default referencing a named range in an external worksheet

    Hi Guys. Hope this is an easy one.

    In my spreadsheet i want to create a macro that will copy in the contents of a named range in an external workbook (preferably without opening the external workbook). I cannot seem to be able to get the notation right to even access the external named range - i get the #name error.
    my external sheet is called 'BANKSORTER.XLS' and the named range is called 'W2DATA' which is a range of about 1000 cells.

    Thanks in anticipation!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    India
    Posts
    627

    Default Re: referencing a named range in an external worksheet

    Assume you wish to copy the named range W2DATA in a sheet named 'MySheet' starting from cell G2
    Assume the Source Workbook is C:\My Documents\BANKSORTER.XLS
    Copy the following code to a module in the destination workbook
    Modify the code as required in respect of SourceBook name and TopLeftCell of the destination range address
    The code does open the workbook but with ScrUpdt off, this actvity is not visible

    Code:
    Sub CopyW2DATA()
      Dim SourceBook As Workbook, CopyFrom As Range, CopyTo As Range
      Set CopyTo = Worksheets("MySheet").Range("G2")
      Application.ScreenUpdating = False
      Set SourceBook = Workbooks.Open("C:\My Documents\BANKSORTER.XLS")
      Set CopyFrom = Range("W2Data")
      CopyFrom.Copy CopyTo
      SourceBook.Close False
      Application.ScreenUpdating = True
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com