Advance filter...VBA macro..

[Natsha]

I have datas in excel sheet the data's, when I filter the category According to the need the glittered dat will be pasted to a new workbook... then macro run, After that I need to filter data's through f- macro code is there in visual basic but I don't know how to code it

F has 3 category always,
submitted & pending approval- should be pasted in one sheet2
Unsubmitted and unassigned both to one sheet-sheet3
Sent back to employee- sheet4

Next step ...through column b employee I'd I will do unique records for all three sheet

Next step- pivot table in sheet(for unsubmitted and unassigned data )
Drag employees to row
Drag age of transaction to values-(drop-down select max)
Rename- row lables as employee name and remove grand total and paste the table in A1 starting cell of the sheet.

Like this I have do for category in j column

 

[Natsha]

Please help...

 

[vletm]

Natsha
I got below alert after I tried to open Your file.


If You would like to get answers then based

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• When drafting a question, try and lay out the question in a clear and concise way.
• Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
• Don't leave out information more info is better

Could You add something, which makes Your challenge to more interesting?

 

[Natsha]

Hi,

Thanks for checking it for helping me..

Ignore my first post..here i have explained clearly

I have macro in the attached workbook , it will copy data based on the unique records in column 6 and paste it to the near by sheets,

How to modify the code according to the below

column 4 has four category

1. sent back to employee
2. submitted & pending approval
3. unsubmitted
4. unassigned

The macro extract data based on unique values but instead of 4 category i need 3 category, like this

1. sent back employee to one sheet
2. submitted & pending approval to one sheet
3. unsubmitted and unassigned to one sheet and rename as Unreconciled

Note: sometimes , sent back to employee will not have but its rarely

After that i will do few steps is that possible to add these code

step1: after data pasted to different sheets , i will go to Data> advance filter>unique records, i will give unique records with column2 ( Employee id) for all three sheets

Step 2 : insert pivot table for unreconciled items and drag employee header to row and age of transactions to values ( drop down select maximum)

Rename -row labels as employee name and remove grand total and paste it to starting cell o the week and paste age of transaction to 4 D column , adding two sheets after A column in pivot table sheet and another column D

so totally we will have 5 column

Step 3 : i have another workbook in folder which is last week worked sheet from there i have do VLOOKUP and extract dats from there

last week sheet: In unreconciled sheet drag employee id and email address to pivot table sheet and from age of transaction sheet in last week sheet drag status with the help of employee id

then change

1. New and No action status to No action taken

2. N/A to New

That's solve, once everything done new workbook sheet should look like last week sheet- hope you get my explanation

Book is one which has macro in it
FM is the last week book...data in that will changes but sheets will not change

 

[vletm]

Natsha
The macro extract data based on unique values but instead of 4 category i need 3 category, like this
If You ask to modify Your code then someone else would do it.
I would use my written code.

After that i will do few steps is that possible to add these code
Okay, then only needed modification is from 4 to 3 categories - or how?

 

[Natsha]

Hii,

If we can create VBA code from strat also I am fine...You can delete the code already present in the sheet and can write from start...i am totally happy with that too..

From column 6 extract data based on three category and paste it to the existing sheet with name of it

sent back employee to one sheet
submitted & pending approval to one sheet
unsubmitted and unassigned to one sheet and rename as Unreconciled

Then I will do the additional steps wich i mentioned..if you can code that also i am fine with that...

 

[vletm]

Natsha
I did a sample code with advance filter.
Press [ Do It ] from Sheet1.
It could need some error-handling too.

 

[Natsha]

Hi thank you,

It works well... But

I have saved the code to personal workbook ..
After that if I simply open a workbook and run the code ita not working.
Is there an way for it ... Because before this steps I have another macro where it create three workbook based on criteria...in that workbook only I will excute this code...so instead of storing macro in one workbook....it would huge help if the code runs in any new workbook

 

[vletm]

Natsha
As I tried to wrote in #3 reply:

• Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
• Don't leave out information more info is better

Your ... but
... did You give those?
About Your Simply ...
Which workbook do You open?
How do You run that code?
You create more workbook before 'my sample code' ... how that related with You challenge ( my sample code )?
Of course 'my sample code' could modify,
( You could try to modify ThisWorkbook to ActiveWorkbook )
but
... I need clear answers to my questions
- - - - if there are still challenges - after above modification - I don't have nor need Your Personal Workbook ...

 

[Natsha]

Hi ...

Sorry for the late reply..

I can understand...but I have somehow managed to rewrite ur code and it's works fine..thank you so much for all the help...