Shared workbook error

[HansV]

When you open both workbooks on the same computer, they are open within the same instance of Excel, so they can communicate directly with each other.
When the dashboard workbook is open on another computer than the input workbook, it obviously runs in a different instance of Excel. This means that the dashboard workbook can only look at the file on disk, not at the changes made in memory. So changes in the input workbook will only be visible when the workbook is saved.

[krishnaa_kumarr88]

thanks a lot

[krishnaa_kumarr88]

Pivot table is updating every 10 seconds, but the rest of the cells are not updating every 10sec. My code for the pivot table is below, but i dont know what will be the code for updating complete worksheet with name Result 1

Sub refresh_pivot_tables()
Dim pt As PivotTable

With Worksheets("Result 1")
For Each pt In .PivotTables
pt.RefreshTable
.Range("L1").Value = pt.RefreshDate 'Last refresh time
.Range("M1").Value = pt.Name 'last refreshed table name
Next pt
End With
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"

End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"

End Sub

[HansV]

Does this work?

Code: Select all

Sub refresh_pivot_tables()
    Dim pt As PivotTable

    With Worksheets("Result 1")
        ' *** Recalculate the sheet
        .Calculate
        ' *** Update the pivot tables
        For Each pt In .PivotTables
            pt.RefreshTable
            .Range("L1").Value = pt.RefreshDate 'Last refresh time
            .Range("M1").Value = pt.Name 'last refreshed table name
        Next pt
    End With
    Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"

End Sub

Or perhaps

Code: Select all

Sub refresh_pivot_tables()

    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"

End Sub

[krishnaa_kumarr88]

I tried ur code, but the excel hang. Its not opening for last 1 hr.
Do you think its because of the code or because of my excel file?

thanks

[HansV]

I don't know. I fear that you're trying to do things that are too complicated for Excel.

[krishnaa_kumarr88]

hmmmm.thanks for ur reply

[krishnaa_kumarr88]

Hi,

Scratching my head over trying to get cells to automatically update every minute.
Brilliant work. Your code is working great for pivot table. But the problem is ,
in my dashboard workbook, i have pivot table and i have cells which calculate how many rows are filled.
That is i have two workbook
Workbook1= have the main datas. It have 5 sheets in workbook1. Sheet 2 is the dashboard sheet which i copied to workbook 2 so that i can leave workbook 2 open all the time.
Sheet2 in workbook1 have pivot table and some cells which link to sheet 1 indicating total number of rows filled in sheet 1.
The problem is i can update the pivot table automatically but not the cells.
any idea?
thanks

[HansV]

Try this line

Code: Select all

    ActiveWorkbook.UpdateLink ActiveWorkbook.LinkSources, xlExcelLinks

[krishnaa_kumarr88]

nah... :(
I have the a COUNTIF function used on one workbook which refers to another workbook, however I get the result #value! unless the other workbook is open - this is even if I chose to update links when I fist open the file.

Do all workbooks have to be open when using COUNTIF?

If I open the other workbook after my workbook with the COUNTIFs on has been opened then all #value! errors disappear and the correwct info is shown.

[HansV]

The functions COUNTIF, COUNTIFS, SUMIF, SUMIFS etc. don't work with references to a closed workbook.
I'd place the COUNTIF formulas in the data workbook, and simply refer to the cells with those formulas in your dashboard workbook.

[krishnaa_kumarr88]

still its not working mate :(
its alright i will do something else
thanks for ur help

[HansV]

I'm out of ideas, sorry.

[krishnaa_kumarr88]

no worries. thanks