Try the attached file.

I defined a name for cell K7 in Pivot Table sheet (FirstCell) , this name is used in code. This will help identify in code the filtered range in a more flexible manner.

Make sure that the filtered range is surrounded by empty cells: for example, K6:O6 should be empty, cells from P7 down, from J6 down.

If you type anything in cell K6 for example, the code will fail. The code is using CurrentRegion to identify the range, and the first rows with headers, it will identify the column to be filtered based on the selected Quarter from slicer.

Hi Catalin,

Wow,the code is really awesome! I learned something new from you 🙂 Thank you!

Do you have any vba course available that can learn for this website? I would like to build up the vba skill also, now i know the vba world is so big 😀

But compare to Power Query,which one is more high demand on the market? I know both of them as two different powerful tool, probably learn both is the best, but kind of budget issue, i wish to choose the most suitable course to learn that can build up my excel skill so that can survive in this competitive environment.

Between, the code works for me, can i just asking one more question i am not sure this concept can be work?

If the top 10 list after select the quarter from slicer, eg :ZZZ,BBB,CCC,JJJ,WWW,QQQ,AAA,XXX,CCC,FFF

The table part usage and revenue as attachment, they are located horizontally together with the top 10 list. I can't make it vertically as there are too many rows in my real data.

If top 10 are sorted, the table part usage and revenue for their rows will be running away if i am having the real data consist with thousand rows.

Can i make the table part usage and revenue also appear the same value as top 10 list, would it possible? Because user after viewed the top 10 list and might want to go back to the two tables to look for the actual value.

Well, Power Query can be very useful, it can be compared with vba in terms of getting the same results in different ways. The course is available on our site. The beauty of Power Query is that it can be mastered in a much shorter time than vba, and the benefits are immediate.

VBA will require years of practice after finishing the basic course, or even a more advanced course, if you're not an experienced programmer, with already tuned programming skills from other languages. We don't have a vba course yet, but we can recommend one from our friends and partners.

Will take a look at the last problems, as soon as possible.

Hi Catalin,

Yes i see VBA is abit complicated than Power Query.

I have programming background and currently dealing with asp.net and vba project, just wish to enhance more knowledge other than asp language.Which vba course from your friend to recommend? I wish to research more info before taking those course.Recently i had watched webinar from Jon at Excel Campus  http://www.excelcampus.com/enroll/ . Not sure does it worth to take the course?

Awaiting your soonest reply for the top 10 question 🙂

Of course, you can try Jon's course. Also, Mynda recommended few weeks ago Allen's course: the Excel Macros for Beginners course

Maybe he has more levels than beginners, choose what suites you.

About your top 10:

I find your way of doing things very confusing. Few ages ago (or about 100 messages ago 🙂 ), you agreed that there is no relationship between those 2 tables, but you are still trying to combine data from those sources, in some calculations. If you decided to combine them, why don't you use the solution you had for merging the data with power query? If you do that, you will have all the data in a single table, in a single report, no need for twisted and complicated solutions. Everything will be simpler as it should be. If I remember well, you have a solution with that calculation inside power query, no formulas needed, just apply the top 10 and that's all.

Your existing setup is not right, because when you apply a filter in the percentage range, rows from first 2 tables will also be hidden, and that should not happen.

Hi Catalin,

Thanks for the recommendation.I will do a research for all the excel courses including this website.

Yes there is no relationship between those 2 tables, and i am following your way to create the GetPivotData formula to calculate on the percentage table.
I've merged the two tables into power query in a single report except the percentage calculation part.So i calculate them manually using GetPivotData on percentage table.
That time had been tried to combine this calculation's column into power query also but failed because the data in those columns on the two tables are not similar.But still the users want to analyse the result with those similar columns to analyse the percentage.

Yes the setup is not right, i was thinking to adjust the table horizontally or vertically but looks not efficiently. Everything is done right now, just the hidden rows issue.They wanted to filter the top 10 table, after that go back to the two tables to look for the data which is in top 10 list. Any idea?

Hi Catalin,

I think i just put the two tables below the percentage table by leaving more spaces between them.I think it shouldn't be overlap the pivots.

May i know how to allow sorting and filter in protected sheet? I am having error on clicking the top 10 button after protected sheet(not allow to filter on protected sheet). I am struggling with this property.When protect the sheet, i have checked all the option including User Autofilter but not working either.

Below is the top 10 macro

================

Sub FilterTop10items()

Worksheets("Customers").Select
Range("B12").AutoFilter--------------------------> error on this line
Range("B12").AutoFilter Field:=10, Criteria1:="20", Operator:=xlTop10Items

End Sub

I created personal.xlsb for the below macro for refreshing the new data.

Sub UnprotectRefreshAll()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="1234"

Next ws

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="1234", _
AllowUsingPivotTables:=True, _
AllowFormattingCells:=True, _
DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True
AllowFiltering:=True, AllowSorting:=True-----------------------------------> not working, cannot sort or filter
'ws("Customers").Range("B12").Autofilter:= True------------------------> not sure which code is correct
Next ws

End Sub

Can you help again?Thanks!

Sure.
At the beginning of the code, Unprotect the sheet:
Sheets("Sheet1").Unprotect Password:="pass"

Then protect the sheet back when the code is done:
Sheets("Sheet1").Protect Password:="pass"

Hi Catalin,

I didn't know can manually protect directly at that worksheet. 😀

Sorry for any inconvenience caused and really tons of thanks to you for being always helpful to help me for every questions.
Appreciate once again!
Thank you. 🙂

You're welcome, glad to hear you managed to make it work as you wish 🙂

Hi Catalin,

Good day to you.

Several years I am back again for the same vba project, related issue but having error as attached. 

I've been using the same code for years without any enhancement and its working well (refresh to unprotect password, make a refresh and protect again) , but suddenly the code is not working well. Unless i separate uncomment and comment again the code for "refresh all" and "protected password" accordingly then it works but it was troublesome.

Can you help to check? Thanks 🙂

Sub UnprotectRefreshAll()

Dim ws As Worksheet

On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets

ws.Unprotect Password:="1234"

Next ws

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets

ws.Protect Password:="1234", _

AllowUsingPivotTables:=True

Next ws

End Sub

Hi,

remove On Error Resume Next, this hides the errors from you, but you need to see them to understand what is happening.

There is nothing I can check, cannot debug a png image. You will have to attach a file or try again yourself.

Is the password still 1234 in all sheets? Should be obvious that if a sheet has another password, will not be unprotected, therefore an error related to sheet protection will show up.

Hi Catalin,

I've removed On Error Resume Next, but still the error pop out. 

All the password is same still 1234, as i tried to run the unprotected code, it can unprotected for all sheets, just when protected code as below pop out the error.

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="1234", _
AllowUsingPivotTables:=True
Next ws

And the weird thing is the error keep popping out, and just now i keep clicking "OK" (about 4 times pop out repeating) then everything back to normal.

I think now just have to think how to disable the error to pop out?

Thanks again!

Regards,

Hooi Thin