Excel VBA Macro: Autofilter And Copy to New Sheet (Dynamic Range)
HTML-код
- Опубликовано: 20 ноя 2019
- Excel VBA Macro: Autofilter And Copy to New Sheet (Dynamic Range). In this video, we create a macro that filters data based on cell value, then copy that data to another sheet. If you ever need to automatically filter data based on another cell and copy only that data to another sheet in your workbook, with Excel VBA, you can use the code we write in this video to automatically filter data pertaining to a value in a cell that can be changed, without having to alter the code. The code specifies which worksheet it will be filtering, the starting point for the filtering, and then what cell will be used to filter by. We can change the value of the cell as we please and filter by whatever we decide. After the filter is applied, only values that we want to see will be visible, and those values will be copied and pasted to another sheet. We go over how to count to number of rows and columns of a set of data, and how to turn off a filter to display all values.
Data used in this video:
gsociology.icaap.org/datauplo...
#ExcelVBA #ExcelMacro
Autofilter And Copy To Multiple Sheets: ruclips.net/video/unUgUlEBvu4/видео.html
thanks
Thanks.
I looked at a few RUclips vids prior to this, yours actually made sense !! and has allowed me to solve my problem.
I'm going to follow you and work through your other vids :)
So glad to hear that!
Great example, thanks! LIKE
Thanks a lot!
I rarely comment on RUclips but I have to thank you for such an excellent tutorial! Your code was clean and easy to follow. I was able to write my first macro successfully thanks to you! The only issue I have is the xlDown command did not reach the end of the rows, possibly due to some blank cells in my database, so i just override with a hardcoded number for rows
So glad to hear that! Thank you for watching!
Awesome. Love it. Thanks Much!
Glad to hear that! Thanks for watching!!
Very good easy and simple, should add more columns to filter
Excellent tutorial
Thank you!
love this
Thanks!
Is there a way to use an "OR' command. If there were, say, 3 columns that represent different things but they have the same type of data in them. Is there a way to filter the data looking for a specific value/name across those three columns?
Hi Greggo, Any chance you can do a tutorial combining this tutorial with sending the filtered data as an email and preferably using email addresses within the workbook. Love your tutorials!!!
Thanks so much for your video , I need help in moving rows from first sheet to second Sheet based on 'D' Column autofilter on "N/A" values which are obtained by doing Vlookup first sheet 'A' column to third sheet 'A' column, i am able to move rows from First sheet to second sheet based on "N/A" values but when i dont have any rows left that means Autofilter on Filter on 'D' Column return no rows , i am getting all rows moved to second sheet. i am using this If sourceworksheet.Range("A:D" & sourcelastrow).SpecialCells(xlCellTypeVisible).Count > 1 for moving rows but not working if there are empty rows for Auto filter.
What if I only want specific columns to be pasted over to Sheet2? E.g. Columns 3, 5 and 7
hello, i am having a runtime error 13 on line *orig.Range(Cells(1, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy* I need it to read and fetch data from column 1 and header is in row 1 I have set the destination to *ActiveSheet.Range("A1").AutoFilter field:=1, Criteria1:=Worksheets(Sheet1).Cells(1, 2).Value* Thanks for an amazing video!
Hi great example, what if I filter it twice and I want to add the data underneath what’s already been pasted?
You can use a variable to store the position under the last row of data thats already pasted, and let that position be the first position for your next paste. I'll make a video for this soon!
Hi.. Thanks for the tutorial.. what if I want to view data for Eastern Europe and Oceania..how do I set multiple criteria in cell (2,6)..
Hi A K! You're welcome! In order to filter multiple criteria in one cell, you could use a comma to separate them like this "Eastern Europe, Oceania". Then you could use the split function to move Oceania to its own cell. From there, you can reference the cells individually in your code when filtering and move "Oceania" back to the original cell afterwards. I go over the split function with filtering in this video: ruclips.net/video/L9BhK6Dejx4/видео.html
thank you your video, it is very helpful, If after filter, the region copy to the Sheet2, Could the "region" more than one and save on the Sheet2, I mean is it possible to save at the one sheet, such as Oceania + Western Europe + Near East...are in the Sheet 2, Thanks your help!
Yes! I'll make a video on that asap!
Hope this helps! Lmk if this is what you are looking for. Thanks! ruclips.net/video/FQyfiKTBKIg/видео.html
@@greggowaffles Thank you very much for your help, I am new of the VBA and try to learn from your video step by step.. your video is simple and easy understand
@@sumcheung5925 thanks! I'm glad to hear that. Feel free to lmk of any other videos you'd like me to make. I'm always open to solving new problems
Hello, Can you show how to copy a ( Manually) filter range to a text file? .Please
wow amazing, i need to filter only 3 regions in column B and copy paste to sheet named "Output" after last used row one by one and those 3 regions will be listed in column Z in source sheet one by one ? how do i do that? can you help , bit urgent
thanks! hope this helps: ruclips.net/video/FQyfiKTBKIg/видео.html
What if your sheets name were the regions. After you filter the regions based on the value of cell F2 you are to copy and paste it on a sheet that has the name same as the value of F2. How would you code that? Do you have a video for that?
Yup! Check this one out: ruclips.net/video/unUgUlEBvu4/видео.html
⭐️⭐️⭐️⭐️⭐️
If I have criteria from e2 to e80 how can I modify them
Hi, What can be done to copy just 1 particular column
Hi! Say you just want column C in this example. You would use orig.Range(Cells(4,3),Cells(count_row,3)).SpecialCells(xlCellTypeVisible).Copy
Hey. Amazing tutorial.
I have a doubt. What if i want to have two criterias like region and product?(though product is not in your example, but if we have data like that)
Or more than two criterias not with comma splitter but by adding another criteria in the next column.
thanks! you can add a Criteria2 to the "ActiveSheet....AutoFilter..." line, use an array or use that line again for more than two criteria
@@greggowaffles But there is a problem doing that. If we write first criteria, it will filter as we press enter, and if we write second criteria, the previous copied data is cleared.
Then if i write code for two criterias, it will run only if both criterias are filled.
How to resolve this?
Thanks in advance!
@@thofiktufel you can remove the "ThisWorkbook.Sheets...ClearContents" line at the beginning so that the data from the first criteria doesn't get cleared
@@greggowaffles Can i have your contact number? Need your little help.
Hello sir
I have one more query if I have one master sheet and one data sheet instead of splitting the data file into multiple sheets partywise how about creating a loop of the master file and autofilter data file as per name mention in master create a PDF of data and so on it will look till the end of record the in master file
Can u guide me
I have already done split file and create pdf
But now I donot want to split I want to filter and create pdf
You can use a count on the sheet that you are pasting the values to, so that you know how far down the data goes and then the next time you copy from the master file you can change the paste code from "output.Cells(1,1).PasteSpecial xlPasteValues" to "output.Cells(count+1,1).PasteSpecial xlPasteValues". I hope that helps!
Yo guys, I have a question:
if count_row returns the number of rows in the table,
and orig.Range ranges from (4,1) to (count_row, count_col),
aren't some of the rows going to be missing since the last row is (count_row + 4 -1).
Any clarification is much appreciated in advance
😄
How to autofilter and copy a table data to Outlook email by VBA Code
Am I correct "Criterials=cells(2,6).value"?
Criteria1:=
Hi Greg, I copied down your code but received this error "ActiveSheet.Range("A4").AutoFilter Field:=2, Criterial:=Cells(2, 6).Value" **runtime error 1004 application defined/object defined error. Please help. thanks a lot
Did you ever figure out the solution I am having the same issue and I am struggling to find the solution
Hi there is problems it is not coping last 2 records of data
hi! at the end of the line with "count_row =..." you can put "+ 2"
Thanks
Sub copy_filtered_data()
Dim count_col, count_row As Integer
Dim orig, output As Worksheet
Worksheets("Sheet1").Activate
Set orig = ThisWorkbook.Sheets("Sheet1")
Set output = ThisWorkbook.Sheets("Sheet2")
count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown)))
ActiveSheet.Range("A4").AutoFilter Field:=2, Criterial:=Cells(2, 6).Value
orig.Range(Cells(4, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
output.Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
Sub copy_filtered_data()
Dim count_col, count_row As Integer
Dim orig, output As Worksheet
ThisWorkBook.Sheets(“sheet2”).Cells.ClearContents
Worksheets(“Sheet1”).Activate
Set orig = ThisWorkbook.Sheets("Sheet1")
Set output = ThisWorkbook.Sheets("Sheet2")
count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight)))
count_row= WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown)))
ActiveSheet.Range("A4").AutoFilter Field:=2, Criteria1:=Cells(2,6).Value
Orif.Range(Cells(4,1),Cells(count_row,count_col)).SpecialCells(xlCellTypeVisible)
output.Cells(1,1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Worksheets("sheet1").ShowAllData
Worksheets("Sheet1").AutoFilterMode = False
Worksheets("Sheet2").activate
ActiveSheet.Range("A1").Select
End Sub
I am getting error 1004 on this line
ActiveSheet.Range("B2").AutoFilter Field:=4, Criterial:=Cells(2, 7).Value
My code
Sub copy_filtered_data()
Dim count_col, count_row As Integer
Dim orig, ouput As Worksheet
Worksheets("BALANCESTOCK").Activate
Set orig = ThisWorkbook.Sheets("BALANCESTOCK")
Set output = ThisWorkbook.Sheets("ORDER_REQUEST")
count_col = WorksheetFunction.CountA(Range("B2", Range("B2").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("B2", Range("B2").End(xlDown)))
ActiveSheet.Range("B2").AutoFilter Field:=4, Criterial:=Cells(2, 7).Value
orig.Range(Cells(2, 2), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
output.Cells(7, 20).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
Hi, My issue was , My table started in column B. Column A was empty, Forr some reason Activesheet.Range was not picking when my data starts from column B. So I moved all the data to one column left. Then my data starts from column A. Now your formula works like a gem. Great, thanks bro