LIST BOX IN EXCEL

[krishnaa_kumarr88]

My code does not work for pivot table close. You have any idea?

[HansV]

In Module 1:

Code: Select all

Public dtmTime As Date

Sub refresh_pivot_tables()
    Dim pt As PivotTable
    With ThisWorkbook.Worksheets("TPM QRQC")
        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
    dtmTime = Now + TimeValue("00:00:05")
    Application.OnTime dtmTime, "refresh_pivot_tables"
End Sub

In ThisWorkbook:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dtmTime, "refresh_pivot_tables", , False
End Sub

Private Sub Workbook_Open()
    dtmTime = Now + TimeValue("00:00:05")
    Application.OnTime dtmTime, "refresh_pivot_tables"
End Sub

[krishnaa_kumarr88]

thanks hans

[krishnaa_kumarr88]

Hi ,
Thanks for your help. I am almost stuck with the following problem. Hope you can give me suggestion please.

Please see the attachment.

This is not the latest attachment. I dont have any problem in updating pivot chart from one excel to another( Note: the attachment is not latest ).
The problem in sheet 2 in row B5 and C5. What i need is, i have total number of machines in Airbus as 17 (that is the list from TPM form). I would like the operators to update all the machines every day. Row C5 in sheet 2 gives information about TOTAL NUMBER OF TPM INSPECTION CARRIED OUT FOR THIS WEEK.
What i need is i would like to update sheet 2 C6 every week starting from zero.
That is from monday till sunday it need to give information for that week. from next monday the cell c6 should go automatically to zero for that week.
Any idea?
thanks

[HansV]

I assume that you meant C6 instead of C5.
Change the formula in C6 to

=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)

[krishnaa_kumarr88]

THANKS A LOT

[krishnaa_kumarr88]

tHANKS A LOT.
i am wondering is there is any way to automatically generate the same formula all over the coloumn.
Say column C i should able to generate the same formula. I dont want to copy and drag it as it slow down my excel.
Do you have any idea?
thanks

[HansV]

Whether you fill the formula down manually or using code, the result will be the same.

[krishnaa_kumarr88]

THANKS

[krishnaa_kumarr88]

Hi hans,
What does this formula do?
=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)

Does this formula will turn the value to zero during weekendS?

[HansV]

No, it simply counts rows that have AIRBUS in column A, and a date from Monday to Sunday (inclusive) in the current week.

[krishnaa_kumarr88]

I am trying to use the below formula
=COUNTIFS('[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!A24:A5555,"AIRBUS",'[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!B24:B5555,">="&TODAY()-WEEKDAY(TODAY(),3),'[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!B24:B5555,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)

but its not working right. Anyidea?

[HansV]

What exactly is the problem?

[krishnaa_kumarr88]

Sorry hans,
Its working now.
My mistake. Sorry abt that

[HansV]

OK, good to hear that.

[krishnaa_kumarr88]

Another problem now hans,
I have two spreadsheet. One is for dashboard workbook which will be open all the time in one big screen and another is the input workbook(shared workbook).
If i use the above mentioned formula in the dashboard workbook, i can see the values only if both the spreadsheet are opened in the same computer. If i close the input workbook(shared workbook) i am not getting any data in the dashboard workbook.

[krishnaa_kumarr88]

i am getting error as #value!

[HansV]

COUNTIFS only works with references to another workbook if that workbook is open in the same instance of Excel.

You could place the COUNTIFS formulas in the source workbook, and use a formula in the target workbook that simply refers to the cell with the COUNTIFS formula in the source workbook.

[krishnaa_kumarr88]

Thanks

[krishnaa_kumarr88]

Hi,
Please see the attachment. In the attachment Please see the sheet TPM QRQC and sheet 1.
TPM QRQC-done by Rudi
Sheet 1- done by myself.
I created pivot table in the same way of how Rudi done. But the format is totally different in Pivot table. I would like to have the same format as u seen in TPM QRQC. What do i need to change in sheet 1 to get the TPM QRQC format pivot table?
thanks