VBA to Copy and Paste Rows if Condition is Met – Excel VBA Example by ExcelDestination




About this tutorial:

Video duration: 10:44
VBA Code to Copy and Paste specific rows from one sheet to another sheet when condition is met can be used on Click Event of Command Button. In this example, Looping concept in VBA along with IF Condition is used to copy entire row if condition is met and paste on another sheet.

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

34 thoughts on “VBA to Copy and Paste Rows if Condition is Met – Excel VBA Example by ExcelDestination

    Riaz Rasool

    (December 25, 2018 - 11:15 pm)

    Hello! Can you help me on this? This is regarding stock trading. If a cell value is 'sell or buy' then it should automatically record the stock's (live current price). And after a while when 'sell or buy' disappears (cell value is empty) it records the changed current price into another cell beside previous record. For example suppose the price keeps changing over a period of time, lets say cell A1 value is Sell/Buy and the price (Cell B1) is 235, so it records 235 and put it in cell C1. After some time if the A1 is empty and B1 value is 200, then it should paste 200 in D1. That means we will have two price C1=235 and D1=200. It should be automated "No button to click" via macro. Is it possible?

    Thanks!

    Abdullah Quhtani

    (December 25, 2018 - 11:15 pm)

    What if there are multiple tables in a sheet separated by texts. How can those tables (only tables) copied or cut and paste-special (without formatting) in another sheet?!

    Mohamed Arafa

    (December 25, 2018 - 11:15 pm)

    Thank you so much for the simple explanation.
    Kindly advise how to use pastespecial rather than only paste

    Alok Goswami

    (December 25, 2018 - 11:15 pm)

    Sir, if i want data in 4 different sheets for 4 different areas then what modification do we need. Please suggest.

    Alok Goswami

    (December 25, 2018 - 11:15 pm)

    I tried to run the same procedures but it shows compile error sub or function not defined. Please help me with it.

    Tray Pas

    (December 25, 2018 - 11:15 pm)

    Brilliant! Thank you for this! So helpful. Is there a way to adjust the code so that I can add more rows to Sheet1, click on the Command Button and NOT have it recopy the previously entered rows? I hope that makes sense. I would like to be able to add more rows and only copy the new rows to the designated sheet. Or is there a function that will automatically filter the entire row to a new sheet based on the value of a certain cell in that row?

    Pilar Schumaker

    (December 25, 2018 - 11:15 pm)

    How would you select specific row from one sheet to another that would only copy from Column A, B and C??

    rajkamal goyal

    (December 25, 2018 - 11:15 pm)

    Sir Mera pass data ha aap apni email I'd send kar da sir pkease

    Ifttt tricks

    (December 25, 2018 - 11:15 pm)

    it seems like is stall, every now and then? it starts ok, but after a few lines it stops. what to do?

    Brandon Hilderbrand

    (December 25, 2018 - 11:15 pm)

    In your video you used the line
    If Worksheets("Sheet1").Cells(i, 3).Value = "North" Then
    Can this be modified to copy any row where Cells(i, 3) is not blank instead of equals "North"?

    Thank you!

    Ifttt tricks

    (December 25, 2018 - 11:15 pm)

    Works like a charm 🙂
    Can you please, tell me how to delete the empty rows that are left behind? and how just to delete a row containing a specific word?
    Thx

    LDEQ_Denise Bennett

    (December 25, 2018 - 11:15 pm)

    I do not want to copy the entire row, only columns A-J. How do I write this into the conditional loop?

    jenesis jeff cariaso

    (December 25, 2018 - 11:15 pm)

    What should i code if this what i want to happen? If textbox is equal to range A1 to A20 then condition.
    Example: A1 value is 00010
    A2 value is 00020
    A3 value is 00030
    A4 value is 00040
    A5 value is 00050
    A6 value is 00060
    A7 value is 00070
    And so on until A20.
    In my user form if the user input in the textbox 00010 then condition.
    My problem is how do i do that ihave 19 else if. Is theres any way to use for lop instead of lots of else if?

    Clayton Rulli

    (December 25, 2018 - 11:15 pm)

    How do you change the value="north" into a variable value, like a reference to a cell on another open worksheet?

    Josiah Waboyo

    (December 25, 2018 - 11:15 pm)

    the code 'a = Worksheets("Sheet1").Cells(Rows.Count, 2).End(x1Up).Row" Code does NOT work work for me . can you help it is giving an error ."run time error 9, subscript out of range'

    Rakesh Verma

    (December 25, 2018 - 11:15 pm)

    "a = Worksheets("Sheet1").Cells(Rows.Count, 2).End(x1Up).Row" Code does NOT work. No idea why it works for you.

    johnflory23

    (December 25, 2018 - 11:15 pm)

    i have something similar but instead of region i have codes in that and i want to move as per the codes in different sheets. I am not familiar with VBA
    Actually i am working on BOQ pages and with the help of the codes given besides the items want to transfer to different sheets (comparison) as per the codes (1-50). if you give me your e-mail i can send you the excel input and output what is required.

    Binesh Balakrishnan

    (December 25, 2018 - 11:15 pm)

    Hi, Thanks for the video. It was very helpful. The code worked like a charm for two days, however after that i'm getting an error at Worksheets("Sheet2").Activate. I'm not able to understand what seems to be the problem. Could you please help me out here. Below is the entire code for your ref
    Private Sub CommandButton1_Click()

    a = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 8 To a

    Worksheets("Sheet2").Rows(i).Copy
    Worksheets("Sheet4").Activate
    b = Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet4").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Sheet2").Activate

    Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet2").Cells(8, 1).Select

    End Sub

    kalvakuntla Shravan

    (December 25, 2018 - 11:15 pm)

    Sub macro1()
    a = Worksheets("boy").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
    If Worksheets("sheet1").Cells(i, 5).Value < 31 Then
    Worksheets("sheet1").Rows(i).Copy
    Worksheets("sheet2").Activate
    b = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("sheet2").Cells(b + 1, 1).Select

    Worksheets("sheet2").Paste

    End If

    Next
    End Sub

    I am unable to execute this one. I would like to paste all the rows which have value more than 31 in column 5 . please help

    SZETO CAROL

    (December 25, 2018 - 11:15 pm)

    Thank you very much, heartfuly . It works!!!

    rgie gaspar

    (December 25, 2018 - 11:15 pm)

    hi what if you wanted to delete the row from the original sheet after copying? thank you

    Virupax Ranebennur

    (December 25, 2018 - 11:15 pm)

    Hi Very nice Video and helpful! just wanted to if i want to paste values what is the command!

    Vishesh Saxena

    (December 25, 2018 - 11:15 pm)

    How can I do the same thing, if I want to conditionally copy from multiple excel and copy it into a separate master excel?

    Jennie Wong

    (December 25, 2018 - 11:15 pm)

    wonderful explanation! thank you so much for sharing your knowledge!

    Simon Kaldau

    (December 25, 2018 - 11:15 pm)

    How can i make it not copy the rows that are allready in the other sheet? like if i copy once, then add more data and then click it again, i only want the new data to be copied?

    Arvind Kesharwani

    (December 25, 2018 - 11:15 pm)

    Sir, I have a problem. I have generated a sheet1 where i have assign a formula in a specific column. I am using a lookup value in another sheet. as i change the value in sheet1 where i assign a formula the lookup value is also change. Sir, I want to know that which formula we use that as i change the value in sheet1 the lookup value will never change in another sheet. Pls do reply my comment, i am waiting for your valuable reply

    Kostas Pachos

    (December 25, 2018 - 11:15 pm)

    Very helpfull, thanks for this, i have only one problem, on my code i have 3 criterias for select the rows to copy and paste, 2 of them are working fine, the first and most important criteria is a date and this is not working, any help?

    seyi otuks

    (December 25, 2018 - 11:15 pm)

    hi for some reason it only copies a few rows from sheet 2 to sheet 1. and the last row it only copies it from column D to H even though i have information from A to H. Please help

    MANISH BHANDARI

    (December 25, 2018 - 11:15 pm)

    I need a macro for following condition
    suppose i have customer excel file in which first 7 rows is for header so, from 8th rowrecords are start
    i need to split rows of 500 record each in one file and save them with name customer1,customer2,customer3,……..
    suppose i have customer file of 2540 records so it split in
    customer1 which have header rows with record starts from 8th row to 507th row
    customer2 which have header rows with record starts from 508th row to 1007th row
    customer3 which have header rows with record starts from 1008th row to 1507th row
    customer4 which have header rows with record starts from 1508th row to 2007th row
    customer5 which have header rows with record starts from 2008th row to 2507th row
    customer6 which have header rows with record starts from 2508th row to 2540th row

    hamed ayub

    (December 25, 2018 - 11:15 pm)

    Can u help me???

    Manesa Duarte

    (December 25, 2018 - 11:15 pm)

    very helpful! thanks. 🙂

    Jerry Hahn

    (December 25, 2018 - 11:15 pm)

    I enjoyed your video. Do you do contract work? I am looking to have something very similar done for work and wanted to see of that is something you would be interested in doing. Please let me know.

    dhananjay kumar

    (December 25, 2018 - 11:15 pm)

    awesome bro…sir I will take ur help if u allow bcz ur concept is crystal clear …. awesome

    Pathum Kumara

    (December 25, 2018 - 11:15 pm)

    Your tutorial is very helpful. But I have a small problem, when i update the master worksheet and then after I click the button it will going to copy all the data again, I wanted to stop that and copy the new records only when i click the button every time i update the master sheet. Can you help me to do that.

Leave a Reply

Your email address will not be published. Required fields are marked *