LIST BOX IN EXCEL
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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.
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.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
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.
The pivot could not sort by colour, but data can be grouped so that the colour is prioritized.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Thats true Rudi.
Thanks anyway :)
Thanks anyway :)
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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.
=IF('[Workbook A]MASTERLIST'!A1="","",'[Workbook A]MASTERLIST'!A1)
Create the formulas only for the sheets and cells that you want to transfer.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
thanks a lot
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
Right-click the TPM QRQC sheet.
Select Move or Copy... from the context menu.
Select (new book) from the To book: dropdown:
Click OK.
You will have to move some of the code to the new workbook, for example the code for the clock.
Select Move or Copy... from the context menu.
Select (new book) from the To book: dropdown:
Click OK.
You will have to move some of the code to the new workbook, for example the code for the clock.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
Please see the attachment. When i try to load any new data , its not going through in right way.
thanks
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
Does it help if you change the refresh_pivot_tables macro in the new workbook to
I specified explicitly that the TPM QRQC sheet is in the workbook running the code (ThisWorkbook).
And I applied consistent indenting to the code.
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
And I applied consistent indenting to the code.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
thanks a lot. its working brilliant
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
do u mean, to add following code in the workbook
Private Sub Workbook_Beforeclose()
stop refresh_pivot_tables
End Sub
thanks
Private Sub Workbook_Beforeclose()
stop refresh_pivot_tables
End Sub
thanks
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
No, take a look at your main workbook and see how the clock is stopped.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
cheers. What difference will i get if i did not stop the pivot table refresh?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
When you try to close the workbook, it will be opened again automatically.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
thanks a lot