Enable a custome view using macro

Hello everyone,
I have a spreadsheet with 26 columns. This sheet is nothing but a status report which contains various columns such as project start date, due date, status, stream, project manager etc. I've created a custom view named “Projstatus” to show only few important columns so that one can get a quick update on the project.
Now I designed a project dashboard in the first sheet of the report to have dropdown boxes (to filter the projects by project manager, status etc) and enabled a macro using CommandButton1_Click().
There are 4 sheets in my report. Sheet 1 - Dashboard, Sheet 2 – ‘Active’ - actual status sheet containing 26 columns. Sheet 3 and 4 with closed projects.
Now I want to have another sheet ‘output’ which just copies the content of Sheet 2 (active project list) based on the filter selected in the dashboard sheet and copy it to a new sheet (with all data validations and conditional formatting enabled as that of active sheet ) and display only few selected columns from the active sheet. Also it should keep the original formatting (dimensions of all the cells) from the active sheet.
Is there a way to do it?
Else it should copy the contents of active sheet to the new sheet and view it in custom view (defined by me).

P.S : I've attached sample VBA code from my spreadsheet.

Any chance you can post a sample of your current workbook? If the workbook contains sensitive data, can you post a replica of the workbook using dummy data? This makes it significantly easier to cater a response

I've attached sample report. All the columns, validation, conditional formatting are valid. Just filled dummy data in the sheet.

Custom view 'Proj Status' has also been defined.

Appreciate your help.

I posted the sample file. Is there a way to solve the issue?

Pranavaraj,

Take a look at my code. I'm not sure if I COMPLETELY understood the request, but the code will take whatever criteria you have chosen for project manager, and move that information into the output sheet. Does this help?

Please Login or Register  to view this content.

Thanks BigBas.
However, I want to display only selected columns in 'output' sheet. If you look at my sample spreadsheet, I have created a custom view named 'Proj Status'. This view displays only specific columns viz, A, B, C, O, Q, S, T, X, Y, Z.
I want my output sheet to display only these specific columns instead of all the columns from 'A' thru 'Z'. Is this feasible?

Thanks in advance!

Hi Pranavaraj,

I had never worked with Custom Views on a programmatic side. I tested a few things out, and none of them seemed to allow me to copy over only the visible selection in a custom view. This leave 2 options. You can create code to delete the columns that you don't want in the output sheet. Option 2 may be to simply create a custom view in the Output sheet that hides the columns in question, then use code to call on that custom view

Thanks Bigbas. I went with option 2 and it worked fine.
However, when I inquire for the second time, the results are not valid since 'Custom View' altered the columns.
Is there a way to clear custom view via VBA?