Conditional copy in Excel using VBA

August 16, 2016 at 06:15:04
Specs: Windows 7
Hey! I'm new to VBA and I need some help.
I would like to conditionally copy some data from sheet 1 to sheet 2.
I want the program to run in a specific column and search for any cells saying 'Yes' (for ex) and then copy the entire row with all the cells containing any data (except, say A1) and paste it in another sheet in the same workbook.

I want it to update the info whenever changes are made too.

Also, I would have figure out a way to do it from sheet 2 to sheet 1. For example, after running the first code, I want the updates/changes to be made to the first sheet too. So basically, updating back n forth.

Any and all help would be really appreciated!

See More: Conditional copy in Excel using VBA

Reply ↓  Report •

August 16, 2016 at 23:48:32
Ok a I have a question.

Say you have the following

        A      B      C      D      E     F
1    Mon      Tue    Wed    Thu     Fri   Yes
2    Mon      Tue    Wed    Thu     Fri   Yes
3    Mon      Tue    Wed    Thu     Fri   No
4    Mon      Tue    Wed    Thu     Fri   Yes
5    Mon      Tue    Wed    Thu     Fri   No

Now in this case I would look at column F and anywhere I find 'Yes' I'd copy that row to sheet2. What happens when I change F5 to 'Yes'? Do I then start the search again from F1? This would mean that all the rows that have already been copied, will be copied across again. Is this what you want?

otherwise what we could do is, have another column 'G' in which we have perhaps a flag or a date stamp to mark that row as being copied. This way it doesn't get copied again the next time the code is run.

Next question.

When you say you want the updates to happen back and forth, I understand what you mean, however what will trigger the update? would it be the change from 'No' to 'Yes'?

Final question, are you sure you want to copy and paste the row and not cut paste it?

We will need to know the structure of your workbook so we can write the code appropriately. Please use the PRE tags to post an example of how your workbook is structured.

message edited by AlwaysWillingToLearn

Reply ↓  Report •

August 17, 2016 at 05:44:29

Thank you for responsing to my question. I have the following info for you:

Consider this the first worksheet (which you can call "database"):

    A     B        C      D     E

1       Name     Sales   Pot   Y/N
2   1   Cornell   1.6   None   Yes 
3   2   Jane      7     PP     No     
4   3   Ladwig    8     NI     No
5   4   BMP      2.1    MB     Yes

Now, when the Column E reads "Yes" , I want that whole row copied to another sheet called "PotPart" (except for column A data as that merely contains serial numbers). Additionally, I want all of this data alphabetically sorted with the criterion: "Name".

Secondly, I could do the update of the cells in PortPart anyway as long as the data is always up to date and alphabetically sorted without any blanks in between. (I would be accessing this command with a button present in the spreadsheet).

Lastly, what i meant with back n forth update was....

Given this data in "PotPart":

    A     B        C      D     E

1       Name     Sales   Pot   Y/N
2   1   Cornell   1.6   None   Yes 
3   2   BMP      2.1    MB     Yes

I want the the whole data row deleted if I change the "Yes" to a "No" without distrubing the sort or leaving any blanks behind.
Additionally, I would like the "Yes" to have been updated to a "No" in the "database" too.

I hope this makes everything clear.

message edited by nananano

Reply ↓  Report •

August 17, 2016 at 06:13:23
Please read my post #1, there were some questions you needed to answer!

Reply ↓  Report •

Related Solutions

August 17, 2016 at 06:25:35
Additionally, this is a copy of the VBA code I have written down so far but it seems to give the following error: Run-time error '424': Object required

Private Sub CommandButton1_Click()

lastrow = Active.Sheet.Range("B" & Rows.Count).End(xlUp).Row The active sheet would be "database"

For i = 4 To lastrow

If Cells(i, 18) = "Yes" Then

Range(Cells(i, 2), Cells(i, 17)).Select



erow = ActiveSheet.Cells(Rows.Count, 2).eng(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 2).Select



Application.CutCopyMode = False

End If

Next i

End Sub

Reply ↓  Report •

Ask Question