Filter data from various sheets

[ROHAN999]

Sir
i have two excel sheets named as "old" and "new"

i want that in my new excel namedas "filter"

when i put a name which exsits on vrious sheets of "old" and "new" excel

where this name exists the entire rows from excel "old" should came in sheet named as "old"
in excel "filter"

and similarly for "new" this should happen

i am attaching samples od dummy data as well as result sample for better understanding

kindly help

thanking you

[ROHAN999]

Kindly help me as per my samples and sample results...

[ROHAN999]

Plz help me..

[apo]

Hi..


You will need to have your OLD.xlsx Workbook and the NEW.xlsx Workbook open...

Click the CommandButton on the Name Sheet (FILTER.xlsm workbook)..

One thing.. you replied to your own thread 2 more times in just a few hours.. there are some negative effects that can have..

For example.. if i am looking at posts .. and see one has 3 replies.. I might think to myself that the Original poster is already getting help and will over look that thread...

Please Login or Register  to view this content.

[ROHAN999]

Thanks for your valuable advice,,i will take care sir

[apo]

Hi..

You could add this to it so it automatically opens the Workbook.. (do same thing for your "OLD.xlsx" Workbook..)

Just replace "C:\YourFolderName\" with your own filepath and folder name..

Please Login or Register  to view this content.

And then this at the end so it closes and saves it afterwards..

Please Login or Register  to view this content.

Also add this at the beginning so you don't get any Warnings about saving files etc..

Please Login or Register  to view this content.

And this at the end return it to normal..

Please Login or Register  to view this content.

[ROHAN999]

Sir

this code is giving runtime error "9"

and the line last row2+workbooks("filter............................

Becomes yellow

kindly help

[apo]

Oops..

Change all instances of:

Please Login or Register  to view this content.

to be:

Please Login or Register  to view this content.

[ROHAN999]

Application.Workbooks.Open ("C:\YourFolderName\" & "NEW.xlsx")

PATH WILL BE THE SAME OF "FILTER" SHEET PATH

SO THIS SHEET PATH ,CAN BE MODIFIED

[ROHAN999]

DID THE SAME AS SUGGESTED BY YOU REPLACED

THERE AFTER ALSO RUN TIME ERROR "1004" COMING

THE COMMAND COULD NOT BE COMPLETED BY USING THE RANGE SPECIFIED,SELECT THE SINGLE CELL...

AND LINE
WORKBOOKS("NEW.xlsx"),sheets......................becomes yellow

[apo]

Hi..

Stop yelling at me (caps lock) ..

This works for me..

Put your FILTER.xlsm file, the NEW.xlsx file and the OLD.xlsx file into a Folder called "Filter" on the root of the C drive..

You will see in the code how it will look for it there... C:\Filter\

Then use this code..

Open the FILTER.xlsm file and press the "Click Me" button..

Please Login or Register  to view this content.

[ROHAN999]

oh i am sorry....sry for the caps lock

my intentions were nt dt..

[ROHAN999]

yes you are right sir..
its working on the sample but not working on my actual dat..i am unable to understand why the above error is coming...

[apo]

Hi..

Attach the file(s) your working with..

[ROHAN999]

in my acctual "OLD" ,"NEW"

THERE ARE DATA IN EACH ROW FROM COLUMN A TO K

AND IN SHEETS THERE ARE LAKHS OF DATA.....I MEAN TO SAY IN SHEET 1 70000 APPROX IN SHEET 2 100000 LIKEWISE

BUT WHEN THE CODE IS RUNNING ON SMALL DUMMY DATA ,AFTER ALTERING AS PER MY NEAD..IT SHOULD RUN BUT I DNT KNW WHY ITS GINING ERROR 1004

REGARDING DATA RANGE

[ROHAN999]

sorry for the caps,,actuaaly i mostly use caps while drafting..so sorry

[ROHAN999]

ok after removing cofidential data i am aatching ..

[ROHAN999]

Sir i am attaching dummy data....

Sheets are protected too..with the password

and password is password

one more problem is that..likewise other sheets are also there from where i copy data ..they allow copying but are password protected and for some sheets i dont know the password

so protect ,unprotect comand cant be used ,as i dnt knw the password..i wnt to jst copyt data from there dont wnat to alteer data there so is it possible

samples of protected and unprotected are attached

[apo]

Hi..

The 1004 error was coming because you had sheets with no data on them.. and autofilter didn't like that..

That's fixed by adding the If Statement you see below in red..

The big issue is that you can't autofilter on a Protected sheet.. so I added code to Unprotect the sheet (then protect it again when done).. again shown in Red below.. but if you don't know the password.. it won't work..

You need autofilter to generate the data that you want to copy to your Master sheet..


You need those passwords...

Please Login or Register  to view this content.

[apo]

Hi..

If you can't get the passwords... another way could be to copy the UsedRange from each sheet in the NEW and OLD workbooks to a Temp sheet in your Master workbook.. and then autofilter there and then copy the results to where they need to go..

That would get around not being able to Autofilter on your protected sheets...

[apo]

Hi..

ok.. the final code..

As you don't know the passwords.. it now copies the data from the NEW and OLD Workbooks to a Temp sheet in your FILTER workbook and does the autofiltering etc there...

Probably next time.. it would be good to mention in your first post.. things like sheets are password protected and password is unknown..

Please Login or Register  to view this content.

[ROHAN999]

Sir

i am using data connection to move the data from protected sheets to new sheets and named them too as old and new

but data connection is taking too much time to update data as the data is huge in each sheet

is there any alternative

[apo]

I don't think so.. if you can't autofilter on those sheets to get the data you want copied across.. your only alternative is to copy it all across and then autofilter it.. which of course is going to take longer as there is heaps more data than if you had of auto filtered it first..

In my opinion.. you have 3 choices..

1. Find out those passwords.
2. Use the copy then autofilter method (takes longer).
3. Recreate your "NEW" and "OLD" sheets (with no password protection on them...) and use them from now on..

If you do option 3.. you can use the previous solution i posted that autofilters it first then copies across...