LIST BOX IN EXCEL

[krishnaa_kumarr88]

Hi Rudi and Hans,

thanks for your help. I sorted out the pivot table issue. My pivot table is working fine and its arranging automatically according to the priority after 1 day of long struggle. I just modified my pivot table in such a way that , priority coloumn is arranged according to the alphabetical order which inturn arrange the coloring as well.

Thanks anyway.

[HansV]

Glad you were able to solve it.

[Rudi]

That is good to hear. As the saying goes: "Where there's a will, there's a way".
The pivot could not sort by colour, but data can be grouped so that the colour is prioritized.

[krishnaa_kumarr88]

Thats true Rudi.

Thanks anyway :)

[krishnaa_kumarr88]

Hi Rudi and Hans,

Thanks for your help. I got stuck in following problem. Hope you might have solution.
Say there are two workbook. WorkbookA and WorkbookB. WorkbookA have all datas including pivot table.(you can see the attachment above in the same thread). WorkbookB is new excel does not have any data. What i am looking for is to remove the pivot table completely from workbookA and to transfer to workbookB. Each time when you update anything in workbookA should reflect in workbookB.
Is it possible?
thanks

[HansV]

You could create formulas in Workbook B of the form

=IF('[Workbook A]MASTERLIST'!A1="","",'[Workbook A]MASTERLIST'!A1)

Create the formulas only for the sheets and cells that you want to transfer.

[krishnaa_kumarr88]

thanks a lot

[krishnaa_kumarr88]

Hi Hans,
Please see the attachment.

In attachment, When you open the attachment , form will open automatically. Please close that form. There are 5 sheets. i got the problem with first 2 sheets.
What i need is, i want to remove the sheet 2 completely. Right now all the red rows in sheet 1 will get reflected in pivot table in sheet2. But as you know i cant share the workbook because it have pivot table. So what i am planning is to transfer the pivot table to seperate workbook and link to sheet1. Say example the attachement workbook as workbook A , i would like to create new workbook as workbookB.
Therefore there will be two workbook. Workbook A i can share it with other workers after removing the pivot table.
WorkbookB i can leave it open all the time as the dashboard. When any one change anything in workbookA(if the row are highlighted red in sheet 1 in workbook A) , i would like to see in the pivot table in the workbook B.
I am really confused. Please help ,
thanks

[HansV]

Right-click the TPM QRQC sheet.
Select Move or Copy... from the context menu.
Select (new book) from the To book: dropdown:

S003.png

Click OK.
You will have to move some of the code to the new workbook, for example the code for the clock.

[krishnaa_kumarr88]

thanks for ur reply. I done the same way few days before, but i am getting error in one of the automatic refresh pivot table.
Please see the attachment. When i try to load any new data , its not going through in right way.

thanks

[HansV]

When I add a module with Result = "REQUIRES ATTENTION" and refresh the pivot table in the other workbook, it appears correctly. Can you give a specific example of what goes wrong?

[krishnaa_kumarr88]

Thats true Hans.
But, when i add module with result and pivot table refresh automatically. Leave the pivot table as it is for 10 seconds, after 10 seconds i am getting run time error. Hope u will get the same

thanks

[HansV]

Does it help if you change the refresh_pivot_tables macro in the new workbook to

Code: Select all

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
    Application.OnTime Now + TimeValue("00:00:05"), "refresh_pivot_tables"
End Sub

I specified explicitly that the TPM QRQC sheet is in the workbook running the code (ThisWorkbook).
And I applied consistent indenting to the code.

[krishnaa_kumarr88]

thanks a lot. its working brilliant

[HansV]

Don't forget to stop refreshing the pivot table in the Workbook_BeforeClose event procedure of the new workbook, similar to the way you stop the clock in the original workbook.

[krishnaa_kumarr88]

do u mean, to add following code in the workbook

Private Sub Workbook_Beforeclose()
stop refresh_pivot_tables
End Sub

thanks

[HansV]

No, take a look at your main workbook and see how the clock is stopped.

[krishnaa_kumarr88]

cheers. What difference will i get if i did not stop the pivot table refresh?

[HansV]

When you try to close the workbook, it will be opened again automatically.

[krishnaa_kumarr88]

thanks a lot