Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Else 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 objworksheet.PivotTables(PivotTableName).PivotCach e.Refresh 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? -- suzetter ------------------------------------------------------------------------ suzetter's Profile: http://www.excelforum.com/member.php...fo&userid=7078 View this thread: http://www.excelforum.com/showthread...hreadid=389517 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 or myworkbook.Names.Add (NameRef, PivotSourceData) and also check taht the sourceworksheet is set ok |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worbook.name not accepting range value
and also check taht the sourceworksheet is set ok Sorry, i meant 'myworkbook' is set ok |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SAVING large worbook | Excel Discussion (Misc queries) | |||
Error message help with linked worbook | Excel Discussion (Misc queries) | |||
Open Worbook and Disable Macros | Excel Discussion (Misc queries) | |||
Increment linked worbook | Excel Discussion (Misc queries) | |||
Print sheets in a worbook. | Excel Programming |