+ Reply to Thread
Results 1 to 3 of 3

worbook.name not accepting range value

  1. #1
    Registered User
    Join Date

    worbook.name not accepting range value

    I have 2 worksheets in the same workbook
    On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32"
    This range value changes due to specific triggers which aren't important
    In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3"
    On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet
    So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to
    When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range
    I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window)
    But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all

    The call to the function looks like this:
    Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")

    The actual function looks like this (I have placed the actual values in comments at the end of the line so you can follow what is happening):

    Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _
    ObjWkSheetName As String, InfoType As String, _
    IRNumber As String, RangeInfoCell As String)

    Dim myexcel As Object
    Dim myworkbook As Object
    Dim sourceworksheet As Object
    Dim objworksheet As Object
    Dim PivotTableName As String
    Dim PivotSourceData As String
    Dim NameRef As String

    Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
    Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
    Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
    Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet

    PivotTableName = "PIR" & IRNumber & InfoType ' PIR1DTCAT

    If sourceworksheet.Range(RangeInfoCell).Value = "None" Then ' C4:L33
    'There is no downtime data, hence the pivot table will not be updated
    PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value ' 'PIR-DT MTH'!C4:L33
    NameRef = "PIR" & IRNumber & "DB" ' PIR1DB
    myworkbook.Names.Add NameRef, PivotSourceData 'This is the line which doesn't work

    myworkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    End If

    End Sub

    Changing the Named range doesn't work
    The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38"
    I have no clue where it gets this from
    And everytime you run the code, the strange range changes
    Even when I hardcode the value (see below) , it still doesn't work
    myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33"

    This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere
    Am I using any predefined VB words?
    Is the sky falling?

  2. #2

    Re: worbook.name not accepting range value

    I do not know exactly what happends but do try first those syntax:

    myworkbook.Names.Add Name:=NameRef, RefersTo:=PivotSourceData


    myworkbook.Names.Add (NameRef, PivotSourceData)

    and also check taht the sourceworksheet is set ok

  3. #3

    Re: worbook.name not accepting range value

    > and also check taht the sourceworksheet is set ok

    Sorry, i meant 'myworkbook' is set ok

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1