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.
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
But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all
call to the function looks like this:
[COLOR=DarkRed]Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")[/COLOR]
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 ' [COLOR=DarkRed]PIR1DTCAT[/COLOR]
If sourceworksheet.Range(RangeInfoCell).Value = "None" Then ' [COLOR=DarkRed]C4:L33[/COLOR]
'There is no downtime data, hence the pivot table will not be updated
PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value '
NameRef = "PIR" & IRNumber & "DB" ' [COLOR=DarkRed]PIR1DB[/COLOR]
myworkbook.Names.Add NameRef, PivotSourceData 'This is the line which doesn't work
myworkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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?