Autofilter And Copy To Multiple Sheets: kzitem.info/news/bejne/1qSLzYmifnSnqGk
@melissagarcia8921
2 жыл бұрын
thanks
@LinhLe-pr2yj
2 жыл бұрын
I rarely comment on KZitem 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
@greggowaffles
2 жыл бұрын
So glad to hear that! Thank you for watching!
@DianeRodgers-v9x
Жыл бұрын
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!!!
@bobjohnson9491
4 жыл бұрын
Thanks. I looked at a few KZitem 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 :)
@greggowaffles
4 жыл бұрын
So glad to hear that!
@swathibharadwaj
Жыл бұрын
Excellent tutorial
@greggowaffles
Жыл бұрын
Thank you!
@lewiskeeffe8649
3 жыл бұрын
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!
@Baigscomputer
3 жыл бұрын
Very good easy and simple, should add more columns to filter
@concordpsap652
3 жыл бұрын
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?
@junnietam
3 жыл бұрын
What if I only want specific columns to be pasted over to Sheet2? E.g. Columns 3, 5 and 7
@vijayasrinivas3200
2 жыл бұрын
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.
@mothekilla
4 жыл бұрын
Hi great example, what if I filter it twice and I want to add the data underneath what’s already been pasted?
@greggowaffles
4 жыл бұрын
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!
@JJSAvi0616
3 жыл бұрын
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?
@greggowaffles
3 жыл бұрын
Yup! Check this one out: kzitem.info/news/bejne/1qSLzYmifnSnqGk
@vaibhavsalvi6532
3 жыл бұрын
Hi, What can be done to copy just 1 particular column
@greggowaffles
3 жыл бұрын
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
@jonnathanjon1
2 жыл бұрын
Hello, Can you show how to copy a ( Manually) filter range to a text file? .Please
@sumcheung5925
4 жыл бұрын
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!
@greggowaffles
4 жыл бұрын
Yes! I'll make a video on that asap!
@greggowaffles
4 жыл бұрын
Hope this helps! Lmk if this is what you are looking for. Thanks! kzitem.info/news/bejne/p4evzJ2BjXR8fJw
@sumcheung5925
4 жыл бұрын
@@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
@greggowaffles
4 жыл бұрын
@@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
@jaikumarnataraj3255
3 жыл бұрын
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
@greggowaffles
3 жыл бұрын
thanks! hope this helps: kzitem.info/news/bejne/p4evzJ2BjXR8fJw
@Jojosmith342
Жыл бұрын
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
@badboybuell123
Жыл бұрын
Did you ever figure out the solution I am having the same issue and I am struggling to find the solution
@srinivasannarayanaswami5378
3 жыл бұрын
Awesome. Love it. Thanks Much!
@greggowaffles
3 жыл бұрын
Glad to hear that! Thanks for watching!!
@andhiemapanoo7444
3 жыл бұрын
Am I correct "Criterials=cells(2,6).value"?
@greggowaffles
3 жыл бұрын
Criteria1:=
@deepakbhanushali1
4 жыл бұрын
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
@greggowaffles
4 жыл бұрын
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!
@AK-ol5hn
4 жыл бұрын
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)..
@greggowaffles
4 жыл бұрын
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: kzitem.info/news/bejne/rW94zn9sfZebq2k
@viveksview7901
2 жыл бұрын
If I have criteria from e2 to e80 how can I modify them
@yidalow8286
2 жыл бұрын
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 😄
@ExcelVisual
4 жыл бұрын
Great example, thanks! LIKE
@greggowaffles
4 жыл бұрын
Thanks a lot!
@thofiktufel
4 жыл бұрын
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.
@greggowaffles
4 жыл бұрын
thanks! you can add a Criteria2 to the "ActiveSheet....AutoFilter..." line, use an array or use that line again for more than two criteria
@thofiktufel
4 жыл бұрын
@@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!
@greggowaffles
4 жыл бұрын
@@thofiktufel you can remove the "ThisWorkbook.Sheets...ClearContents" line at the beginning so that the data from the first criteria doesn't get cleared
@thofiktufel
4 жыл бұрын
@@greggowaffles Can i have your contact number? Need your little help.
@นาบีมัมหมีน้องโดยองตลอดไป
3 ай бұрын
How to autofilter and copy a table data to Outlook email by VBA Code
@jluis859
2 жыл бұрын
love this
@greggowaffles
2 жыл бұрын
Thanks!
@shoaibrehman9988
4 жыл бұрын
Hi there is problems it is not coping last 2 records of data
@greggowaffles
4 жыл бұрын
hi! at the end of the line with "count_row =..." you can put "+ 2"
@shoaibrehman9988
4 жыл бұрын
Thanks
@redhaakhund1271
3 жыл бұрын
⭐️⭐️⭐️⭐️⭐️
@Jojosmith342
Жыл бұрын
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
@samirbouaziz5916
3 жыл бұрын
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
@girifilmmaking6240
3 жыл бұрын
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
@girifilmmaking6240
3 жыл бұрын
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
Пікірлер: 58