Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    london, Gtr London, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 2000)

    I have a pivot table pointing to some data, which is in a named range. I am copying in some new data and trying to change the range accordingly so it refers to the new dataset. I cannot understand why the first method listed below does not work but the second does.

    1) Use an object reference: NewDataSet.Name = "SR_data", NewDataSet is an object reference created using Set NewDataSet = Selection.CurrentRegion and "SR_data" is the named range.

    2) Selection.CurrentRegion.Name = "SR_data"

    The attachments below contain all the relevant code and data. The file entitled 'Named_Range_ex' contains the VBA code, and the original data with the named range. The other file contains the new dataset, which is copied and pasted with a view to the named range being updated to reflect this new dataset.

    Any help would be much appreciated.

  2. #2
    New Lounger
    Join Date
    Jun 2003
    Location
    london, Gtr London, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    Thanks Hans

    I think you did answer my question, which is good as it was bugging me

    In your reply you also stated that is more efficient to use 'something' with range objects. I assume that you missed a word out and are referring to object references and the fact that it is more efficient to use these than than activate and select.

    Let me know if my understanding is correct.

    Regards
    Tim

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    <P ID="edit" class=small>(Edited by HansV on 26-Nov-03 15:33. Removed typo.)</P>I don't know the exact reason, but I think that the problem occurs because NewDataSet is not in the active workbook when you set its name. I notice that there is a lot of activating and selecting going on in your query. It is often more efficient to use Range objects instead of Selection. The following code works for me:

    Dim LastRowOrig As Long
    Dim LastRowNew As Long
    Dim DiffRows As Long
    Dim NewDataSet As Range
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet

    Set wbkSource = Workbooks("Named_Range_ex_data.xls")
    Set wshSource = wbkSource.Worksheets("SRs")
    Set NewDataSet = wshSource.Range("D2").CurrentRegion
    LastRowNew = NewDataSet.Rows.Count ' not used?
    NewDataSet.Copy

    Set wbkTarget = Workbooks("Named_Range_ex.xls")
    Set wshTarget = wbkTarget.Worksheets("SR_data")
    wshTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
    LastRowOrig = Range("SR_data").Rows.Count ' not used?

    NewDataSet.Name = "SR_data" ' this works for me

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    Hello Tim,

    I have corrected my reply and removed "with" twice. It was a leftover from an earlier version of the sentence (I had "work with", changed "work" to "use" and forgot to remove "with") So what I wanted to say is simply that in most circumstances, using a Range object is better than using Selection.

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Location
    london, Gtr London, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    Hans

    I have tested the code below and it still does not work and I am getting the same problem.

    Apologies for not doing this earlier but your reply made perfect sense, because the code did work when I tried copying in data from a different sheet in the same workbook rather than a different workbook.

    Regards
    Tim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    What exactly doesn't work? Do you get an error message, or is the name assigned to the wrong area, or ...?

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Location
    london, Gtr London, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    When I say that the code does not work I mean that the named range entitled 'SR_data" is not updated when the following code is run: ' NewDataSet.Name = "SR_data". The named range continues to refer to SRs!$A$1:$D$17 rather than SRs!$A$1:$D$21. However the named range is updated if I use 'Names.Add Name:="SR_data", RefersTo:=NewDataSet.Name'.

    Thanks for your tips regarding the use of range objects rather than Activate/Select etc.

    Regards
    Tim

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using an Object Reference Reassign a Named Range (Excel VBA/ Excel 200

    I think you are confusing the two workbooks. NewDataSet is a range in Named_Range_ex_data.xls; the SR_data name in this workbook already refers to SRs!$A$1:$D$21, and the macro only set the name to this same range.

    The SR_data name in Named_range_ex.xls refers to SR_data!$A$1:$D$17, but you don't change that by doing something to NewDataSet in Named_range_ex_data.xls. By the time you execute Named.Add, Named_range_ex.xls has become the active workbook, so this does change the range in that workbook.

    If you were to define a range in Named_range_ex.xls and set a name for that range, it would work as intended.

Posting Permissions

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