HomeОбразованиеRelated VideosMore From: Dinesh Kumar Takyar

Copy auto filtered data to another worksheet automatically with VBA

207 ratings | 40408 views
How to copy auto filtered data to another worksheet automatically with VBA. Complete VBA code available here: http://www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-vba/
Html code for embedding videos on your blog
Text Comments (45)
Ignacio Muñoz (8 hours ago)
Necesitaba conocer la dirección del rango filtrado. y después de muchas busquedas, hallé la solución aquí. Muchas gracias por compartir sus conocimientos
Dinesh Kumar Takyar (1 hour ago)
Muchas gracias
kurban ali (11 days ago)
Good morning sir I have seen your video, I have one Excel worksheet where the data is more than 5 lakes. I have to use filter in 3 collunm user ID, transect ID and the name filter copy make new folder new workbook paste save and close. I had made one VBA code which one is taking that much of time how much is required to do manually. Can you help me on that? How to do in short time.
Dinesh Kumar Takyar (11 days ago)
You can try advanced filter: https://www.exceltrainingvideos.com/extract-data-from-one-excel-worksheet-to-another-using-advanced-filter-with-vba/ How can I make my VBA code run as fast as possible? Here are a few tips: 1. Be sure to declare all your variables as a specific data type. (Use Option Explicit in each module’s Declarations section to force yourself to declare all variables.) 2. If you reference an object (such as a range) more than once, create an object variable using the Set keyword. 3. Use the With-End With construct whenever possible. 4. If your macro writes data to a worksheet and you have lots of complex formulas, set the calculation mode to Manual while the macro runs (but make sure you do a calculation when you need to use the results!). 5. If your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False. Search https://www.exceltrainingvideos.com/
محمد أبو عبدو (2 months ago)
KAUSHIK SAVLA (6 months ago)
I send a file to 100 people to input their data. All 100 people provide me information for their respective data. Now I collate these data by opening every workbook and filtering non blank. I copy this data and paste it in another new workbook. How can I get it automated? My column to filter remains same and it's just vba opens all the files saved in specific folder and filter for non blank and copy the data and paste it in one workbook one below another? This will help me to save 4 days Manuel work which sometime causes error too in case something missed to track/change
KAUSHIK SAVLA (6 months ago)
+Dinesh Kumar Takyar Sir I tried the link. What is happening it is only capturing 4 column however I have data in 72 column. Further it is not capturing full data of all files only couple of lines it is taking copy/paste. Pls suggest
Dinesh Kumar Takyar (6 months ago)
These links will guide: https://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ https://www.exceltrainingvideos.com/delete-blank-rows-from-excel-worksheet-using-reverse-for-next-loop/ Or search https://www.exceltrainingvideos.com
vemulapalli sharmista (6 months ago)
Hi sir,As shown in the above video all the columns are copied from one sheet to another but is there any way to pick up one specific column?
Dinesh Kumar Takyar (6 months ago)
This link will guide: https://www.exceltrainingvideos.com/how-to-copy-column-data-into-another-workbook/ Or search https://www.exceltrainingvideos.com
Amz Chagar (7 months ago)
Hi, I am getting a Run-Time error '91' Object variable or With block variable not set. This occurs With ActiveSheet.AutoFilter.Range. do you have any idea why this has happened? thanks in advance
Venkatesh Sony (8 months ago)
Hi Sir ,i want to copy one particular cell which is in filters and i want to paste that particular cell in another sheet, can you please help me
Dinesh Kumar Takyar (8 months ago)
Give an example.
Amol Dive (8 months ago)
Can we add cell value in critera
Aree Kittimanaphan (10 months ago)
Thank you.
pawan mishra (10 months ago)
How can I get all filter criteria?
Liazul Islam (1 year ago)
Dear Sir, I am Liazul Islam, I am a great fan of you. I used this Technic and its work. But when there is no filter result, I mean filter criteria1 not match then it copy others data row. How can i solved it.
Liazul Islam (1 year ago)
Dear Sir, Thanks for replaying me. I use your code on my another workbook. It is creating customer Ledger. I have 3 data sheet which i get from my sql server. I am trying to create customer Ledger this data sheets.
Dinesh Kumar Takyar (1 year ago)
Have you placed apostrophes before the two lines of code like so: 'Sheet2.Range("A1") = "name" 'Sheet2.Range("B1") = "salary" You should remove the apostrophes. Or. check your code again because if the criteria is not met the autofiltrng will be nothing and you should receive the message: "no data available for copying!" This link will help: http://www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-vba/
Abdul Hafeez Kayani (1 year ago)
Sir Firstly I appreciated your work and efforts in Excel VBA and Now I want to copy active row from shit one in sheet two in next available blank available sheet by Short key like there are various rows of data in sheet one I find the data with find tool on row 50 and want to past this row in sheet two with short key of Ctrl+space is it possible
Aviram Jena (1 year ago)
Hi Sir, What would be the VBA code to auto filter my data if my criteria is current week plus 2 weeks ahead. for example if I am trying to filter my data on date 18.01.2018 then it should auto filter current week i.e., 14,01.2018 and 2 weeks ahead i.e., 21.01.18 and 28.01.18. so when I run the vba in any other week then it should automatically filter that particular current week plus 2 weeks ahead. Please help.
bheemesh manikonda (1 year ago)
Hello Sir, I follow your training's regularly. I am trying make automate my testing regular status report which we need to take from Test Automation management tool HP ALM then after we have to do some modifications in excel. Here the task is , once i import data from ALM , we have defects primary status & Severity in two columns. here primary statuses data column does have (Assigned,Closed,Deferred,Fixed,Open,Rejected,Reopen).& Severity column data statues like (Critical,High Medium,Low ). Here the task is i need to find out primary statues count for each severity status wise. after that we have to place them in separate sheet in the form grid. the grid is designed like in form of horizontal & vertical shape. Horizontal row contains Primary status and vertical column contains Severity data. Can you help me out how data is divided in earlier sheet and place them in second sheet .?
Akash Anant (1 year ago)
there is error showing ["run time error "9" subscript out of range] while runnin' the code and debugging goint to the worksheets("sheets2)".cells.clear...help me pls asap
Dinesh Kumar Takyar (1 year ago)
Check your code carefully.
albert fernandez (1 year ago)
Nice video, thanks!!!! I have question, do you know how to filter an array in VBA???
Nikhil Khandelwal (1 year ago)
Hello, I have a quick question on this. Instead of filtering on column Salary, i want to filter on column A i.e. Name and based on all the names in column A i want to create different cuts within the same worksheet. for ex. if i filter on name "Ram" then all the data related to ram should be copied to a new sheet with Sheet name as Ram. how can we do it?
Dinesh Kumar Takyar (1 year ago)
Dinesh Kumar Takyar (1 year ago)
This link will guide: https://www.exceltrainingvideos.com/advanced-filter-2/
Shiv Kumar (2 years ago)
hi Dinesh sir i have question how can we get filtered data into another workbook?
angelito adoptante (2 years ago)
Very helpful vidz sir,Congrats!Here is my case now.i am a bookkeeper and trying to create my own workbook.I have an accounts receivable column in sheet 1.How can i transfer payment received from certain client that i recorded in my accounts receivable column to sheet 2 which is the accounts receivable ledger.clients pay number of times per month.Please help using VBA.thanks a lot and more power...
a.rakesh patro (3 years ago)
very usefull sir. Can we give criteria for a particular date to filter the data?
Deepak Kamtikar (3 years ago)
Hi sir. I have a query. Here is an example: cell A1 contains value 20. Cell A2 = A1 / 2. Now I would like to develop a function which does goal seek like this: For what value of A1, A2 = say, 100. My Goal Seek value is in cell A3.I can do this using "data - what if analysis - goal seek". But I would like to develop a function with three parameters ( SetCell as range, GoalValue As Double, ChangingCell As Range) which when entered in A4, will change values in A1 & A2 accordingly. Deepak Kamtikar
Ashraf Anwar (3 years ago)
Hey Dinesh Sir! I want to ask that, can make any ledger in excel as like other accounting software, that we can get opening balance and ending balance, periodic view or as per sheet changes
Dinesh Kumar Takyar (3 years ago)
+Ashraf Anwar These links might help: http://www.exceltrainingvideos.com/financial-statements-in-excel/  http://www.exceltrainingvideos.com/expense-statements/
Raghavendra Rao (3 years ago)
Correct. Even me also did not understand the logic behind offset property (.rows.count,-1,1) can you please explain? Thanks in advance
Poker Minion (2 years ago)
+Raghavendra Rao Hello Raghavendra. I saw your VBA posts on this blog: http://www.myengineeringworld.net/2014/05/pdf-search-through-vba.html I appear to be having the same problem that you had regarding the automation error. Could you email me please as I'm hoping you may have found the solution. [email protected] Thanks.
Raghavendra Rao (3 years ago)
+Dinesh Kumar Takyar Thank u sir for making me understand
Dinesh Kumar Takyar (3 years ago)
+Raghavendra Rao The Resize property makes the range a specific number of rows and columns larger or smaller so that we can copy the filtered range only.
Ameer Ali (3 years ago)
is there any simple process without VB , i want to get the total entry in the first sheet , for ex sheet 2 (day 2 )+ ,sheet 3(day 3) + sheet 4 (day 4) etc, ?? thanks
Dinesh Kumar Takyar (3 years ago)
+ameer ali This link might help: http://www.exceltrainingvideos.com/consolidate-excel-worksheet-data-using-copy-autofill/
Krish (3 years ago)
Thank you Sir, Can you please explain logic on "Resize(.Rows.Count - 1, 1)"
salmon petrus (3 months ago)
I'm not clear yet.. too. Is that command to select row by row ? if yes, how if we want to print it directly row by row or in a range let say 60 row (60 times print) till the end of visible row ? Thankyou, Sir.
Krish (3 years ago)
Thank you sir.... :)
Dinesh Kumar Takyar (3 years ago)
+sai krishna The Resize property makes the range a specific number of rows and columns larger or smaller so that we can copy the filtered range only.
Forza Algiers (3 years ago)
Thank you .
Eric Balanza (3 years ago)
thank you sir it help so much =)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.