Excel: IF Cell Contains Text Then Copy to Another Sheet | Copy Cell Based on Condition

[@AfricanHope]

Hello Chester, you literally saved me hours of my time. I am currently working on my PhD and it’s things like this that I appreciate the most. I appreciate what you are doing here on KZbin 🌻 God bless you!

[@IvanCortinas_ES]

Excellent Chester. Nowadays I think the more useful method is with the function FILTER. Thank you for these tutorial!!!

[@kryspinjemioek3235]

This is pure gold. I tried to find something like this for hours. I have just one question - what should I change in "If Status = "something" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell" in order to select and copy/paste not a whole row but only first cell in that row?

[@rocioocana3324]

Thank you! Easiest, fastest way to get it done!

[@indiabernicefoster1617]

Thank you. I was looking for the second option. It worked perfectly and now I feel like a wiz.

[@triosofttechnologies1532]

you literally saved me hours of my time. Thank you .

[@serenaparini7097]

Hello Chester, this was extremely helpful, short and clear! I've been looking on the internet in other languages hours and hours but couldn't find any help. Thank you so much, wishing you an amazing new year!

[@DavidWilliams-cw7wc]

Really useful - many thanks Chester for sharing!

[@jluispereira]

Thanks for the excellent tutorial and VBA script. Both turned out very useful to me! 👍🏻

[@thiedi16]

Chester, Great info. What if you wanted to do for 2 ranges. I.e. over budget and departments?

[@foodietheprincess5766]

Thank you so much for this! One of my employees asked me if I could pull over only certain data from another sheet and I had no clue how to do that. I was able to use your VBA code and modify it to bring in just what I needed including modifying the code in step 4 to include 12 columns and cells. It worked like a charm and she thinks I'm a genius LOL. I know only enough to get into trouble :)

[@pablobascunanc.20]

Can you share what you modified to just copy some of the columns!?

[@ronaldkoons6522]

Brilliant... It's one thing to vomit loads of information about complex Excell formulas. It's quite another to do so and actually make sense.

[@billmc5457]

Clear code and explanation. Thanks!

[@angadade]

Thank you for these tips and VBA tutorial. Could you also suggest a VBA if there are multiple columns to be filtered separately by different criteria (not that if multiple criteria are met in different columns but if a single column has certain criteria met it should return value from corresponding cell of a particular column and likewise for the other columns but if different criteria condition is met. E.g. If column B has some blank cells corresponding values of cells in column A should be filtered and copied, likewise if for column C specific date is not present corresponding values of cells in column A should be filtered and copied. Thank you in advance.

[@stephanisat]

Thanks so much. I just needed a simple sign in sheet to be pulled from a master sheet. This cleaned up the functions I had already put in there. It makes everything so much easier.

[@scottgray7825]

Hello Chester. Thank you very much for sharing your knowledge, you make your videos very easy to follow and understand. Thank you. To follow on from this video how would we add to the VBA code to make this copy and paste of cells from one sheet to another sheet an automatic process where it identifies a name in column B and then as a result it automatically copies that row to the next line in the sheet with the same (criteria) corresponding name? I am trying to build a workbook where the 1st page receives the daily customer traffic inputs under the date and as they are input each row is copied to the corresponding customer sheet that shows there transactions only over the different days? Any help/advice will be greatly appreciated.

[@shihadop]

tnx Chester, you saved me time

[@markhuitson]

Hello - Perfect - However, one thing............Is there a way to alter the macro to only copy CERTAIN columns? I have 100 columns of data but i only want to copy over 5 columns to my new sheet?

[@jemtl]

Thanks Chester. How can I prevent that this script is not copy Duplicate Entries in a Range. When adding new entries in sheet and run the script it just copy the same input and the new one.

[@ramy2695]

This is great and what I've been looking for, Thank you so much! However, my data set that I'm filtering contains formulas, and I'm quite new to VBA. How can you copy/paste the values, not the formulas? Right now I only get #REFs where the values are supposed to be. I hope you understand what I mean :)

[@somethin4mary]

This was so helpful. Thanks

[@Rkeev1]

Love the macro; Advanced Filter works too

[@davidguite2611]

Hi This is great thanks and is almost what I need for a task. Is there a way to instead of looking for a string of text, it performs the task if the cell just contains any value so basically isn't blank?

[@lexitaylor2203]

Thank you Chester! The VBA Formula seems to be just what I am looking for, however is there a way to prevent it from copying duplicates? For instance, when I use a button to update the information it would only pull any new items and not just continue to list the same items.

[@IBFInvestments]

i was able to complete this but your only running yours on one rule - BUDGET - what if i have several items in a column that have different wording that needs to copy over to a sheet?

[@dumm]

Thanks Chester, so peaceful

[@spumonicapicola8333]

Thank you, Chester! For some reason, when I try to use the copy method that should copy the entire row, I get the VB Error box with "Run-time Error '1004': Copy method of Range class failed." When I debug it takes me that line of code: "If Status = "Over budget" Then Status.EntireRow.Copy PasteCell" I've changed all the variables to match my requirements. If you have any thoughts, I'd appreciate it.

[@sharonhumphrey2403]

Thank you for this video, I have watched several videos today and this one has got me the closest to where I want to be. However using the VBA it is not copying the whole line across. I wonder if you can help?

[@MaximumZebra]

Very well done and super helpful. Keep up the good work!

[@mattwilson7168]

Using the second option, is there a way to have it filter multiple things? So if it contains "1", "2", "3," "7", "8".. ect

[@nunobakker5942]

Thank you for your video's Chester, it has helped me a lot. I am struggling with one thing now though, perhaps you have a video about this that I cannot find? I have copied data like you did in this video. I would however like the copied data to be updated in the original cell when changed in the copy. (With other words, have it update in both cells when either of them are changed) Is this possible? Perhaps with some VBA code?

[@losandez]

Chester, I appreciate you posting this. What would you recommend if I wanted to look at specific columns? Meaning, I need information from Columns A, B, C, D but need to skip E, F, and G and continue on with H, I, and J? I still need the row information just minus columns E, F, and G. Thanks again!

[@antonynaude3372]

Hi Chester, could you explain what exactly is happening in this portion of the VBA. 'If Status = "Over budget" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell, more specifically from Then Status.Offset... I am trying to modify this based on my dataset. Also is it possible to get the VBA to rather copy a specific cell into another cell on the same sheet but on the same row where it found the matching criteria? I.e. if it found "Over Budget" on A5, copy B5 to C5.

[@6204ar]

I have a little problem, the rows that are being copied to the new sheet have formulas and formats transferred literally. How can I make so that just the "value" or "text" of certain cells are copied just as they are?

[@catscratchmagazine83]

Hi! This code is great! I set up an automatic macro call timer that also seems to work, except each time it refreshes, it duplicates the first entry. How do I only get the macro to move NEW entries into the linked sheet? Thank you!

[@Jojosmith342]

Hi Chester, is there any way to teach us how to find the same data between 2 sheets based on primary key AND copy this same data to another sheet automatically? thanks a lot

[@lovestorycreates]

Thanks for this! Time saver indeed! Im working on a project management system for my team. I'm trying to have a row copied over from a main page if a certain name is selected from a drop menu. It would be copied to another worksheet where that person can see all of their projects. The issue im running into is the VBA is only copying one row over and not if multiple project rows are assigned. Any thoughts on why that could be or how I could adapt my code? Thank you!

[@donpablu]

Thanks a lot for this!

[@vishalwalia3457]

Hey Chester, thank you so much for this video... It helped me on my data. I need another help where I am looking to copy the TEXT from Cell A1 on sheet 1 to the cell A1 in sheet 2 if cell B1 is equals to YES on sheet 1. Can you please help?

[@noelsimon77]

Hello Chester Thanks for this it has really helped me, but could I go a step further is their code that would identify certain characters or even a word within the column. So instead of searching for Over Budget it would search for "Ove" or "Over"? and copy the data Thanks in advance Noel

[@billpallan1006]

Excellent piece of code

[@JosephWong-kx3oi]

Excellent !!

[@yimindeng8721]

Thank you thank you thank you!!!

[@josephhengoed7646]

This is excellent! I'm running into an error though where the VBA method macro is only pulling the first and last row that meet the criteria and skipping all rows inbetween.

[@melodyrelody5632]

Did you ever find a solution for this?

[@Faceless-Man-]

Hi, have several worksheets containing information for staff members. Each worksheet is formatted the same but some cells will have different values. What I am trying to do is set it so that if an entire row has a specific value in one of the cells/columns it will copy that entire row to a new sheet. This is the code that I have used using your guidance and there are no errors when I run it but nothing is pasted across to the new sheet, this being sheet 16. The only rows that I want to copy is if they have the number "1" in one of the columns: Option Explicit Sub copy1Records() Dim StatusCol As Range Dim Status As Range Dim PasteCell As Range Set StatusCol = Sheet8.Range("F10:F50") Set StatusCol = Sheet4.Range("F10:F50") Set StatusCol = Sheet1.Range("F10:F50") Set StatusCol = Sheet12.Range("F10:F50") Set StatusCol = Sheet13.Range("F10:F50") Set StatusCol = Sheet2.Range("F10:F50") Set StatusCol = Sheet5.Range("F10:F50") Set StatusCol = Sheet9.Range("F10:F50") Set StatusCol = Sheet14.Range("F10:F50") Set StatusCol = Sheet15.Range("F10:F50") Set StatusCol = Sheet11.Range("F10:F50") Set StatusCol = Sheet3.Range("F10:F50") Set StatusCol = Sheet7.Range("F10:F50") Set StatusCol = Sheet10.Range("F10:F50") Set StatusCol = Sheet17.Range("F10:F50") Set StatusCol = Sheet18.Range("F10:F50") Set StatusCol = Sheet19.Range("F10:F50") Set StatusCol = Sheet20.Range("F10:F50") Set StatusCol = Sheet21.Range("F10:F50") Set StatusCol = Sheet22.Range("F10:F50") Set StatusCol = Sheet23.Range("F10:F50") For Each Status In StatusCol If Sheet16.Range("F2") = "" Then Set PasteCell = Sheet16.Range("F2") Else Set PasteCell = Sheet16.Range("F2").End(xlDown).Offset(1, 0) End If If Status = "1" Then Status.Offset(0, -4).Resize(1, 5).copy PasteCell Next Status End Sub This is all new to me so any help would be appreciated. Thanks

[@dudeyme3331]

How can I copy paste the valve to another work sheet According to the date pls make a video for it (example revenue report daily it varry and cell row as well as )

[@nebojsadurmanovic2268]

Awesome, thanks!

[@NiceAussie]

Thanks. But are do any of these methods provide a dynamic filtered output. ie.when the original data is changed, does the filtered data reflect the change automatically?

[@stephanisat]

I used the second method (the FILTER function), and it does update automatically.

[@arijturner8419]

what does it mean offset (0, -5) resize (1,5) ?

[@lanki2610]

Hi Chester. so i have a task, that i cant seem to solve... but i think you might know how. - Its a project overview status document, it needs to copy a row (the most recent status) to another workbook, and overwrite the row with same project number (or insert as new row) in this way we could all send our recent update on projects to another document, that holds all the projects for the school. - please let me know if you have already covered this topic, im searching the web for everything and all.

[@ludlug2401]

god job. very helpful.

[@raikosiewert2744]

Dear Chester. Thank you very much for this great video. Regarding your Macro I am wondering if it would be possible using "Insert Copied Cells" instead of the usual "Paste". This would paste a new row just beneath the row where the condition is true without deleting anything else below. Is this sowehow possible in VBA? How would the code be?

[@fendisaya6748]

Hi..pls advise how to paste it as just value..not the whole formula

[@highschoolwallaywalasamina4987]

WHAT IS THE excel formula for search a keyword in a row then copy that specific cell to another place?

[@chrisgrol5829]

This is driving me crazy. My office wont let us use VBA in our excel sheets. So I am trying this filter function, but can't get it to work. I have a project tracker, and I want it to populate different sheets based on the type of project it is (ie. forestry projects go to the forestry sheet etc.). However, when I do this using my project type column (where each line is labeled as "forestry") it will not populate the forestry sheet. Just gives a Value error. I'm at my wits end and may have to try and do this manually moving forward which will be incredibly wasteful as far as time goes. Great video, just I seem to be doing SOMETHING wrong in the filter function

[@Mr_Turtle1369]

Thanks!

[@ChesterTugwell]

Many thanks Hunter

[@kurt-y9l]

so I have to use this filter on coloms up to GO

[@cristianomasciulli6472]

Thank you. 👍🏻

[@AnotherFishyMuffin]

how can I change the vba code to skip duplicates (so i can run the macro to update the sheet without deleting all the cells every time)?

[@An-Old-Dude]

What if I want to populate only rows that have text in a specific column. =FILTER('Job List'!A6:Q42,'Job List'!I6:I42="") What would I put in the last set of quotes?

[@majedulhoquechowdhury18]

Hi Chester, this video is Very helpful for me, many thanks to you.could you please help me in the scenario, when i want to filter a collection of data by matching a part of cell text data not whole part of text in the cell. EX.... in cell contains "LBL-1100" and i want filter it using "LBL-", because the "LBL-" is constant and another pert is variable and can be any text that i don't know. please give me solution using Filter function.

[@qaisarmayo5367]

hello Sir i have seen and searched lot of videos and playlist for my query but not found suitable solution. actually i want to shift a row automatically up or down on the basis of dates and grade or cast. For example my name is placed on 5 row as worker or on the basis or appointment seniority and i was promoted to the post of Manager. Here i would like that when i changed the post and date of promotion my data automatically move top of the list on seniority one.

[@rachellover6434]

Hi Chester, having some difficulty getting this working for something I am trying to create would I be able to get some help with it at all? I know you are likely very busy but it would be appreciated massively

[@khalidyusufalkirwi]

Thanks, I tried the filter but the filter does not work if the "over budget" words were embedded or among other words in the cell

[@tilokchandhuidrom8063]

Sir, I would like to save if two or three column match then it's corresponding cell value to the another target cell address using excel vba

[@tylergornak4939]

For some reason when I use the macro I only get the first and last rows that meet my data requirement moved to the new sheet, not every row that meets requirements. Any help on why?

[@samlalomia3747]

This was a great help! Thank you. I do have one question. I get it to run but it isn’t returning all the records it should. It is only pulling 5 records over when there are about 20 it should be grabbing. I haven’t figured it out just yet. Any thoughts? Sub CopyOverBudgetRecords() Dim StatusCol As Range Dim Status As Range Dim PasteCell As Range Set StatusCol = Sheet4.Range("P5:P200") For Each Status In StatusCol If Sheet5.Range("A6") = "" Then Set PasteCell = Sheet5.Range("A6") Else Set PasteCell = Sheet5.Range("A5").End(xlDown).Offset(1, 0) End If If Status = "Phase 2" Then Status.EntireRow.Copy PasteCell Next Status End Sub

[@IBFInvestments]

Hello - i want to do this but i want to move the row instead of copy - can you please help me with that VBA Code

[@abuibrahimalim8838]

On running the code, I am getting only the first and the last result from the criteria (Over Budget). Can anyone tell me why? Any help in this will be highly appreciated. Thanks.

[@anbooker]

I keep getting a Run-time error '424' Object required on this line Set StatusCol = Sheet11.Range("B2:B100"). However that is where my criteria info is. What am I doing wrong?

[@Melissa-em9bi]

I am trying to populate a gnatt chart based on a cell criteria on another sheet. I don't want to copy the whole row just a cell in the row. If the cell has a number >1.0 I would like it to populate the name of the instrument only not the entire row. Is this possible?

[@mumbaigirl952]

If I want to attached over budget mail how to do it

[@samdesai27]

What if you only wanted col A and C data pasted? and if we wanted to copy into a specific column?

[@ellenw6024]

Is this just for ecel

[@jamessylaire9141]

Hello I need your help please on a excel document. I am struggling for days... Please tell me if you can help me.

[@talulabell19]

Where do I find the VBA?

[@alializadeh8195]

Thanx

[@tipsywolf5466]

I am using 2019 excel but i dont have =FILTER in my excel... how i can get it ??

[@charlottetrewartha]

Hello, Did it just like you said, but it hasnt worked :/ Comes up with #CALC! empty array..... Please help Dx. did it with very simply spreadsheet, household chores, was trying to filter high priority... but failed, tried to fix it still nothing

[@vaishaliyadav5009]

is it possible if A1 =1 (value) and B1 >0 than copy B1 value in C1 ( C1 is destination )

[@marcigrey]

Hmm....this isn't working for me, I'm trying to find the cells with the value "Transfer". Instead the code is copying rows of data with all the values listed in the column. Also, would like for this not copy over data that is already existing on Sheet2.

[@bolesey]

Hi Chester, this is really helpful, but wonder if you or one of the community can help? I’m trying to adapt your VBA for copy and pasting the entire row to CUT and paste VALUES, but I’m getting nowhere. Any tips would be much appreciated! Thanks.

[@lyladrayson5680]

I am having same problem - copies all the formatting from my source table when all I want is values, can't figure out how to modify PasteCell

[@darrenminnick9168]

@@lyladrayson5680I’m having the same problem. Did you find a solution to this?

[@jazzyou5334]

when i save my code, it saving but after i close all the application the excel and open again the code is gone

[@bandile126]

I have 88 unique categories to copy to different sheets from a sheet with 6385 rows I want to automate this based on contents of 1 column ( with the 88 unique categories)

[@SunflowerSyla]

I keep getting a "invalid next control variable reference" when trying to use this macro

[@raileatimms8635]

I can't seem to find the VBA code

[@gekalskip]

but now i dont want to copy paste the entire row, but just certain cells ... how to do that ... i guess ill never know

[@dhananjaysinhrayjada6471]

I am applying this formula but SPILL error is coming

[@cathygates6459]

Trying your script and LOVE this! can't get last line to work. I want to do a copy pastecell but I need the paste to a pastespecial as it's copying a line that has a formula in it. Ideas? Option Explicit Sub CopyOverCalculatorLines() Dim StatusCol As Range Dim Status As Range Dim PasteCell As Range Set StatusCol = Sheet2.Range("A2:I51") For Each Status In StatusCol If Sheet3.Range("A5") = "" Then Set PasteCell = Sheet3.Range("A5") Else Set PasteCell = Sheet3.Range("A1").End(xlDown).Offset(1, 0) End If /this is where i'm having issues as it pastes the formula from the other sheet and then the formula doesn't work. I get a 0 If Status = "Include" Then Status.EntireRow.Copy PasteCell Next Status End Sub

[@jamessylaire9141]

May I send you the file and Explanations please ?

[@redhaakhund1271]

👍👍👍👍👍

[@undrawingend]

How could this code be edited to ignore blanks ? Been trying to figure this out to speed up a task in work that is repetitive and know it can be done easier ( and gain time back) trying to copy data from multiple sheets within a set range ( -5 working days from todays date - each sheet is in the format did.mum.yy with do not use) that is based on a data sheet. Any guidance would be appreciated as will allow me to do more when in work as a lot of my time is taken up by having to change the sheet