If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course
Thank you Sumeetji, I really look forward each week for very useful excel tricks and tips. May I please request you to provide a link to download your spreadsheets.
Thank you for posting this informative video. I was able to import the bonus for each marketing employee using the name and also positionally (with the table that contains just bonus values) using Power Query: no formulas and no VBA. Would be great to see how you'd approach these problems using Power Query. Thank you kindly.
Yeah, can also be done using PQ, but seemed like an overkill unless you're already working with data in PQ. I have not tried it, but I assume it can be done using the name column as the key to combine two tables. And without names, it can be done using an Index column. I will give it a try. If you can share how you did it, that would good learning for all
Thank you kindly. Since I started learning Power Query ~ 6 months ago, I have tried to use it in solutions that normally use formulas. Too bad UA-cam doesn't accommodate posting images. I will try to describe the steps I took: 1) On the web page provided in the description, I did not see a link to download the file for practice. So I took a screenshot of the table with headers [Name], [Department], [Code], and [Bonus] (named "Employees"). I also took a screenshot of the table with headers [Name] and [Bonus] (named "Name and Bonus"). I also took a screenshot of the table with header [Bonus] (named "Bonus"). 2) I used Power Query to extract the data from each of these tables into Excel. 3) I loaded each of these tables into Power Query. 4) I changed the data type of each of the columns in each table to Whole Number or Text, as appropriate. 5) I performed a left outer join between Employees and Name and Bonus tables, on the [Name] field. I then expanded the linked table and selected the [Bonus] column, thus adding it to the Employees table for the Marketing employees. This was the FIRST report. 6) I then created a query named "Mktg_Emp_Indexes" by filtering Employees table on [Department] = "Marketing", with columns [Name], [Department], and [Index], starting at 0. 7) I then created a query named "Indexed_Employees" by performing a left outer join between Employees and Mktg_Emp_Indexes". After expanding, the Employees table now has an [Index] column populated with the indexes from Mktg_Emp_Indexes for the employees who work in the Marketing department. All other rows in the Employees table have null in the [Index] column. 8) I then added an [Index] column, starting at 0, to the Bonus table. 8) Finally, I performed a left outer join on the Indexed_Employees and the Bonus tables on the [Index] column, thus bringing in the values from the Bonus table into the Employees table. This was the SECOND report. 9) Both reports have the bonuses properly matched to the Marketing employees but the order of records in the first report somehow changed while performing the merge. Not sure if any of this is clear. It would be easier to show screenshots of the various queries. Thank you kindly.
Great tips, some I know. I have this situation often, it's quite fiddly, why MSoft has not come up with a better solution like ctrl paste or something...?
Thanks Summit. Quick question: in the VBA code: Set destinationcells = Destcells.Offset(1,0).Resize(destinationcells.rows.count) what confuses me here is that resize part because the destinstioncells number will be the same size as the original destionacells so you are increasing the number of rows of the destionacells isn't? Can u please clarify ?
Since I am using this in the For statement, it runs this statement everytime. So if I don't spcidy Resize(destinationcells.rows.count), it would Set destinationcells to one single cell (destCell.Offset(1, 0)) and the loop completes after 2 runs
@@nadermounir8228 Yeah, I see the issue. The destination range keep getting bigger, but it still works as I exit the For loop. Let me see if I can make the code better
@@nadermounir8228 Have a look at this one. It solves the issue of destination range shifting with each loop iteration Sub PasteintoFilteredColumn() Dim visibleSourceCells As Range Dim destinationCells As Range Dim initialDestinationLastRow As Long Dim sourceCell As Range Dim destCell As Range Set visibleSourceCells = Application.Selection.SpecialCells(xlCellTypeVisible)
Set destinationCells = Application.InputBox("Please select the destination cells:", Type:=8)
@@trumpexcel Thank you Sumnit. Now the pasting cells not increasing in size. Just one more thing : can we write this: IntialDestinationLastRow = Destination.Rows.Count.Row instead of Destination.Rows(Destination.rows.count).row ?? I am not very good in VBA but thought I would ask this ?
Hi, @trumpexcel I Did the vlookup for the filtered cells but now i want to remove the formulae for vlookup as other rows have other formulae in my excel, how can i remove the vlookup, breaklinks will not work as the vlookup is from the same workbook..Any guidance will be helpful
Thank you for the content. When copying data from another sheet or workbook using the VBA model, can you only copy one column at a time or can you do multiple? I followed the steps in the video but only the first column I had copied made it to my filtered data (I had 8 columns copied). Thank you for your help!
I can't find an answer to this online! All my search criteria seems to be off! I'm trying to (simply) add new data (any data) to a filtered column so that the new data now appears in said column for future filtering. I can't seem to remember how to! Thanks!
UPDATE! I literally just typed the data in the cell under the filtered column, and now see it becomes part of the filtered data! Lol. I did say 'simple'. Lol. Thanks!
Referring to % similarity of text string. Please check link for more m.ua-cam.com/video/7_C9lEUHM10/v-deo.html&pp=ygUcU2ltaWxhcml0eSBmdW5jdGlvbiBpbiBleGNlbA%3D%3D
Isn't this just a bug? What's the use case for copying only visible cells but then pasting into hidden cells, that justifies it being default behaviour? It just makes using the software that much more difficult and increases the likelihood of mistakes and data corruption. Requiring VBA for what should be simple copying and pasting? It should be the other way round, where VBA is required for copying visible cells and pasting into hidden cells in filtered ranges.
Thanks Iván.. True, this is a pain point. I wish regular copy paste could work in this scenario. The work around are fine, but seems too much for something that should be easier.
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Excellent analysis. This happens more times than one can think. Thanks for sharing, Sumit.
Nice video Bro.. That's what I had been looking for many days.. ❤
Thanks Kuldeep... Hope the video is helpful 🙂
Thank you for ALL of the videos, I am learning so I can do more things at work like making schedules.
Glad you are finding the videos helpful 🙂
Thank you for usual clever tricks.
Glad you found the video helpful 🙂
Thank you Sumeetji, I really look forward each week for very useful excel tricks and tips. May I please request you to provide a link to download your spreadsheets.
Thank you Rajeev for the kind words...Have added a link of the download file in the description of the video
@@trumpexcel Thanks Sumitji for your kind response. Really appreciate.
Very important tips. Thanks a lot.
Thanks Satwant.. Glad you found the video helpful 🙂
How you will copy and paste from another sheet?
encountered the same issue lol
Me too same issue
Great video!
Finally a working VBA code. You saved my day!
sumit ji , thanks a lot for sharing your brilliance in a very easy and lucid ways making all of us finer excel users ❤
Thank you so much for the kind words Vipul ji... Glad you found the video helpful 🙂
Thank you!! This is the only video explaining the situation accurately.
Thank you for posting this informative video. I was able to import the bonus for each marketing employee using the name and also positionally (with the table that contains just bonus values) using Power Query: no formulas and no VBA. Would be great to see how you'd approach these problems using Power Query. Thank you kindly.
Yeah, can also be done using PQ, but seemed like an overkill unless you're already working with data in PQ. I have not tried it, but I assume it can be done using the name column as the key to combine two tables. And without names, it can be done using an Index column. I will give it a try. If you can share how you did it, that would good learning for all
Thank you kindly. Since I started learning Power Query ~ 6 months ago, I have tried to use it in solutions that normally use formulas.
Too bad UA-cam doesn't accommodate posting images. I will try to describe the steps I took:
1) On the web page provided in the description, I did not see a link to download the file for practice. So I took a screenshot of the table with headers [Name], [Department], [Code], and [Bonus] (named "Employees"). I also took a screenshot of the table with headers [Name] and [Bonus] (named "Name and Bonus"). I also took a screenshot of the table with header [Bonus] (named "Bonus").
2) I used Power Query to extract the data from each of these tables into Excel.
3) I loaded each of these tables into Power Query.
4) I changed the data type of each of the columns in each table to Whole Number or Text, as appropriate.
5) I performed a left outer join between Employees and Name and Bonus tables, on the [Name] field. I then expanded the linked table and selected the [Bonus] column, thus adding it to the Employees table for the Marketing employees. This was the FIRST report.
6) I then created a query named "Mktg_Emp_Indexes" by filtering Employees table on [Department] = "Marketing", with columns [Name], [Department], and [Index], starting at 0.
7) I then created a query named "Indexed_Employees" by performing a left outer join between Employees and Mktg_Emp_Indexes". After expanding, the Employees table now has an [Index] column populated with the indexes from Mktg_Emp_Indexes for the employees who work in the Marketing department. All other rows in the Employees table have null in the [Index] column.
8) I then added an [Index] column, starting at 0, to the Bonus table.
8) Finally, I performed a left outer join on the Indexed_Employees and the Bonus tables on the [Index] column, thus bringing in the values from the Bonus table into the Employees table. This was the SECOND report.
9) Both reports have the bonuses properly matched to the Marketing employees but the order of records in the first report somehow changed while performing the merge.
Not sure if any of this is clear. It would be easier to show screenshots of the various queries.
Thank you kindly.
The Fill left option resolved my issue. Thanks!
Great video Thank you!
Great tips, some I know. I have this situation often, it's quite fiddly, why MSoft has not come up with a better solution like ctrl paste or something...?
Thanks man you saved my couple of minutes in manual work. V lookup worked for me❤
Thanks Summit. Quick question: in the VBA code: Set destinationcells = Destcells.Offset(1,0).Resize(destinationcells.rows.count) what confuses me here is that resize part because the destinstioncells number will be the same size as the original destionacells so you are increasing the number of rows of the destionacells isn't? Can u please clarify ?
Since I am using this in the For statement, it runs this statement everytime. So if I don't spcidy Resize(destinationcells.rows.count), it would Set destinationcells to one single cell (destCell.Offset(1, 0)) and the loop completes after 2 runs
@@trumpexcel my concern is that Destinationcells will be larger this way and the loop may not end ?
@@nadermounir8228 Yeah, I see the issue. The destination range keep getting bigger, but it still works as I exit the For loop. Let me see if I can make the code better
@@nadermounir8228 Have a look at this one. It solves the issue of destination range shifting with each loop iteration
Sub PasteintoFilteredColumn()
Dim visibleSourceCells As Range
Dim destinationCells As Range
Dim initialDestinationLastRow As Long
Dim sourceCell As Range
Dim destCell As Range
Set visibleSourceCells = Application.Selection.SpecialCells(xlCellTypeVisible)
Set destinationCells = Application.InputBox("Please select the destination cells:", Type:=8)
Application.ScreenUpdating = False
initialDestinationLastRow = destinationCells.Rows(destinationCells.Rows.Count).Row
For Each sourceCell In visibleSourceCells.Cells
For Each destCell In destinationCells.Cells
If destCell.EntireRow.Hidden = False Then
sourceCell.Copy
destCell.PasteSpecial Paste:=xlPasteValues
If destCell.Row < initialDestinationLastRow Then
Set destinationCells = destCell.Offset(1, 0).Resize(initialDestinationLastRow - destCell.Row)
End If
Exit For
End If
Next destCell
Next sourceCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
@@trumpexcel Thank you Sumnit. Now the pasting cells not increasing in size. Just one more thing : can we write this:
IntialDestinationLastRow = Destination.Rows.Count.Row instead of Destination.Rows(Destination.rows.count).row ??
I am not very good in VBA but thought I would ask this ?
Very Nice tips for pasting data in filtered column, I was struggling to do this but by your this video my problem is solved easily. Thanks for this
Glad the video helped 🙂
Excellent, thank you...
Thanks so much. It has caused me headache for years.
Thank you so much Sir
Hi, @trumpexcel
I Did the vlookup for the filtered cells but now i want to remove the formulae for vlookup as other rows have other formulae in my excel, how can i remove the vlookup, breaklinks will not work as the vlookup is from the same workbook..Any guidance will be helpful
Thank you for the content. When copying data from another sheet or workbook using the VBA model, can you only copy one column at a time or can you do multiple? I followed the steps in the video but only the first column I had copied made it to my filtered data (I had 8 columns copied). Thank you for your help!
Great VBA code, I have tried it and it works for single columns. But can it be altered to paste multiple columns at once?
I am having the issue too. Were you able to find a solution?
@@BlakeAbbott-s7u not yet
Thanks Sumit!!
Was looking for this for years ❤
How about if you need to copy paste data in different excel sheet ?
Could you please make it work for multiple columns data selection and paste into filtered columns through VBA
Excellent piee of code. I as looking for such a soultion for a very long time.
Very useful video. Thank you.
Glad you found the video helpful 🙂
what about copying values from cells that contain formula?
How do you do this with different sheets because thats what I'm trying to do and its not working.
Thanks for knowledge share 🎉 it worked
I can't find an answer to this online! All my search criteria seems to be off! I'm trying to (simply) add new data (any data) to a filtered column so that the new data now appears in said column for future filtering. I can't seem to remember how to! Thanks!
UPDATE! I literally just typed the data in the cell under the filtered column, and now see it becomes part of the filtered data! Lol. I did say 'simple'. Lol. Thanks!
Awesome 👍
JESUS CHRIST THANK YOU FOR THIS VIDEO DUDE. ❤❤
Good - Informative Content. - Keep up the Good Work !!
When we delete the second sheet,Data gone from main sheet how can we solve this
Brother your video saved me thank you
Glad the video helped 🙂
excellent. thank you
Hello Sir,
Please help with similarity function.
I am not aware of any SIMILARITY function in Excel. Are you talking about partial match of fuzzy match?
Referring to % similarity of text string. Please check link for more m.ua-cam.com/video/7_C9lEUHM10/v-deo.html&pp=ygUcU2ltaWxhcml0eSBmdW5jdGlvbiBpbiBleGNlbA%3D%3D
This is not an inbuilt function in Excel, and someone has created it using User defined function in VBA
Will you please help with the VBA?
Help ful😊
great and thanks, I will try the google one.
Hi Sumith, really handy, can you share the code.
Hello Sudesh... You can get the VBA from here - trumpexcel.com/paste-into-filtered-column/#Using-VBA-works-in-all-scenarios
Thank You
Thank you so much
thank you, bro
Thanks
Cool Mate!
Isn't this just a bug? What's the use case for copying only visible cells but then pasting into hidden cells, that justifies it being default behaviour? It just makes using the software that much more difficult and increases the likelihood of mistakes and data corruption. Requiring VBA for what should be simple copying and pasting? It should be the other way round, where VBA is required for copying visible cells and pasting into hidden cells in filtered ranges.
I agree... This should be the default option. Sadly it isn't, so we need to resort to these workarounds
❤❤❤❤
😊😊
Jesus loves you brother
my excel crashed TT
Just waste of time , please don't watch this video if you are beginner
Thank you captain obvious
You didn't have to make a 10-minute video.
Excellent analysis. This happens more times than one can think. Thanks for sharing, Sumit.
Thanks Iván.. True, this is a pain point. I wish regular copy paste could work in this scenario. The work around are fine, but seems too much for something that should be easier.