Auto Insert a new row if a condition is met

March 6, 2018 at 10:51:24
Specs: Windows 7
Hello everyone,

I saw a topic that is very in line with what I need, but I do not know how to change the proposed solution. --> https://www.computing.net/answers/o...

The solution proposed is very similar to what I need to do, the difference is that I need to add a conditional prior to copying the data.

A brief summary:
There is a base worksheet with different clients (rows) and several columns.One of the columns is a binary answer 'Yes' or 'No'.

In another worksheet (lets call it 'Clients Yes)', I need to have all the clients that are flagged as 'Yes' on that specific column of the base worksheet.

I have the option to add manually each client that is 'Yes', but it is very time-consuming and subject to errors.

What I would need: when a client that has the specific category flagged as 'Yes' is added to the base worksheet, a new line on this 'Clients Yes' worksheet is created and some information (columns A and B) of the base worksheet is copied to this 'Clients Yes' worksheet'.

Thank very much for your help

message edited by luccatatoni


See More: Auto Insert a new row if a condition is met

Report •

#1
March 6, 2018 at 11:05:11
rephrasing the question naming the worksheets and columns

Base Worksheet ("Base")
Column A - Group
Column B - Company
Column F - Ext. Rtg. -> binary answer 'Yes' or 'No'
Column H - Ticker


'Client Yes' worksheet (named as 'Rtg Base')
Column A - Group
Column B - Company
Column C - Ticker


What I need:
When a new line is added to the Base worksheet and the column F is flagged as Yes, a new line on 'Rtg Base' worksheet is created and the columns and the information of the columns A, B and H are copied to the columns A, B and C of the Rtg Base worksheet.

Thanks again!


Report •

#2
March 6, 2018 at 11:45:38
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRw As Long

'Determine if Yes was entered in Column F
  If Target.Column = 6 And Target = "Yes" Then
  
'Fill Rtg Base cells with data from Target Row in Base sheet
    With Sheets("Rtg Base")
      nxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(nxtRw, 1) = Cells(Target.Row, 1)
        .Cells(nxtRw, 2) = Cells(Target.Row, 2)
        .Cells(nxtRw, 3) = Cells(Target.Row, 8)
    End With
  End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#3
March 6, 2018 at 12:15:37
Thank you very much DerbyDad03

I am sorry for taking your time on this, but I am not being able to insert your code

I am trying the following:

alt + F11
right click on the Base worksheet (sheet6)
insert > module
paste your code provided above
F5

then the 'Macro' box (with a list of all the macros of the spreadsheet) appears and I do not know how to proceed after this step. What should I do?

Kind regards
Lucca


Report •

Related Solutions

#4
March 6, 2018 at 12:57:03
In the case of a Worksheet_Change macro, it needs to reside in the Worksheet module, not a Standard module.

Right click the sheet tab for the Base worksheet and choose View Code. That will open the Worksheet module for that specific sheet. Paste the code into that module.

The code will now "monitor" the Base worksheet and run when any change is made. The first instruction is an If statement so the creation of the new row in the Base Rtg sheet will only occur if the 2 conditions of the If are True.

There is subtle difference between the code running and the code actually doing anything. The key thing to remember is the code will run when any change is made to the Base worksheet. However, the first thing it will do is execute the If statement. If it determines that both conditions have not been met, it will simply exit. If both conditions are True, then the other instructions will be executed. In both cases, the macro ran, but in one case it didn't do anything that was evident to the user.

BTW...you can also get to the Sheet module for any sheet by using F11 to open the VBA editor and then double clicking the Sheet Name in the left hand column. If you look in the title bar at the top of the VBA editor, it will say something like Book1 - [Sheet1 (Code)] as opposed to Book1 - [Module1 (Code)]

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
March 7, 2018 at 04:26:31
Thank you very much DerbyDad03

I am now having the following error:

Run-time error '13': Type mismatch

When I click on 'debug' it highlights the following:

 If Target.Column = 6 And Target = "Yes" Then 

Do I need to change anything else?
kind regards
Lucca

message edited by luccatatoni


Report •

#6
March 7, 2018 at 06:30:32
What are you doing/trying to do at time that the error is popping up?

The line that is producing the error is checking 2 things at once. You could try splitting the If's as shown below to see if that isolates the issue to one of the 2 If's or perhaps even resolves the issue. Without more detail, I can't address/replicate the error.

If splitting the If's resolves the issue, I would still like to know what you were doing at the time that the error was produced. I might learn something that will help me prevent that type of error in the future.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRw As Long

'Determine if Yes was entered in Column F
  If Target.Column = 6 Then
   If Target = "Yes" Then
  
'Fill Rtg Base cells with data from Target Row in Base sheet
    With Sheets("Rtg Base")
      nxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(nxtRw, 1) = Cells(Target.Row, 1)
        .Cells(nxtRw, 2) = Cells(Target.Row, 2)
        .Cells(nxtRw, 3) = Cells(Target.Row, 8)
    End With
   End If
  End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
March 7, 2018 at 07:14:12
That works great now! thanks again DerbyDad
I was inserting each amount one by one

one thing that i noted - recapping the columns

Base Worksheet ("Base")
Column A - Group
Column B - Company
Column F - Ext. Rtg. -> binary answer 'Yes' or 'No'
Column H - Ticker


'Client Yes' worksheet (named as 'Rtg Base')
Column A - Group
Column B - Company
Column C - Ticker


when I fill the column F with Yes, the values are copied to the Rtg Base, but when the column H is filled, nothing is copied -- however, if I fill the column H before the F, I get the data of column F copied.

I was able to understand that as soon as I fill 'Yes' on the column F, the macro is activated and the data are copied, however what I fill after triggering the 'Yes' is not copied.

is there any way to model this? otherwise I can switch the columns in order to avoid it


Report •

#8
March 7, 2018 at 08:31:39
re: "I was inserting each amount one by one"

I can not think of a single reason why entering data in one cell at a time would cause a Type Mismatch error. If you told me that you were changing multiple cells, such as deleting a block of data from a range of cells all at once, I might see the error occurring. In any case, I'm glad the updated version worked for you.

re: "I was able to understand that as soon as I fill 'Yes' on the column F, the macro is activated and the data are copied, however what I fill after triggering the 'Yes' is not copied."

First, let's make sure that you understand what I tried to explain in Response #4. You may already understand it, but since you only addressed "half" of the way a Worksheet_Change macro works, I'm going mention both halves so that there is no misunderstanding.

This is 100% correct:

"as soon as I fill 'Yes' on the column F, the macro is activated and the data are copied"

But this is also correct:

"As soon as I make any change to the Base sheet, the macro is activated"

You'll note that I left off the "and the data are copied" part. This is key. The macro fires with any and all changes. It then evaluates the If's and determines whether or not it should do anything else.

That should help you understand why "what I fill after triggering the 'Yes' is not copied"

Let me explain it in detail just to be sure:

When a change is made to a sheet, Excel passes some information related to the change to VBA. The main thing that happens is that an entity known as the Target is created. Contained within that entity is the address of the cell that was changed. Within that address in the Row and Column of the Target. The entity also contains the value of the cell that was changed, as well as a lot more information.

Now, since Column 6 (F) contains your "Yes" choice, it's safe for me to assume that any additional data that you add or change after selecting Yes is not in Column 6 (F). Therefore the code is going to fire on that change (because it fires on any and all changes) but the very first instruction (If Target.Column = 6) is going to be False because the Target is in some other column. Therefore the code is going to exit. It is never even going to look at any other instruction. It's going to jump directly to the corresponding End If, then End Sub. Therefore the only time that the data will be copied is when:

1 - The Target is in Column 6 (F)
2 - The Target contains Yes

In other words, both If's must be True for the Target that gets created for that specific change. If either one is False, nothing will be copied.

OK, so now that you (hopefully) understand all the gory details behind a Worksheet_Change macro, let's address your "changes made after Yes is selected" question.

If you want the code to copy data if a change is made after you have already selected Yes in Column F, you have changed the game significantly. Let's say you select Yes in Column F and then change Column A. If the code copied the row at that point, what would happen if you then changed Column B? The code would copy the row again, placing it below the row that was copied previously. If that's what you want - a new row in Rtg Base for each and every change in Base - that's easily done, but that's not what you asked for. If you only want a single row that encompasses all of the changes for that row, then you have 2 choices:

1 - Automatic Copy: Use the current code and choose Yes in Column F only when when you are sure that you are ready to copy the row.
2 - Manual Copy - Don't use a Worksheet_Change macro. Use a "regular" macro and run it manually (perhaps via a button) after all changes to the row have been made.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
March 7, 2018 at 09:37:25
that is very clear now - thank you very much for the clarification.

In fact, I guess that a manual copy ran by a regular macro would work better on this case - and I am sorry for not making it clear since the first post.

to switch the Worksheet_Change to a 'regular', what changes should I do on your proposed code?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRw As Long

'Determine if Yes was entered in Column F
  If Target.Column = 6 Then
   If Target = "Yes" Then
  
'Fill Rtg Base cells with data from Target Row in Base sheet
    With Sheets("Rtg Base")
      nxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(nxtRw, 1) = Cells(Target.Row, 1)
        .Cells(nxtRw, 2) = Cells(Target.Row, 2)
        .Cells(nxtRw, 3) = Cells(Target.Row, 8)
    End With
   End If
  End If
End Sub

Thanks again!


Report •

#10
March 7, 2018 at 09:59:01
I would need to know what you want to copy. Since this isn't a Worksheet event macro anymore, there is no Target entity to work with.

The simplest method might be to copy the row that contains the ActiveCell or Selection. Either of these should work, assuming that the selected cell(s) is in the row that contains the data that you want copied.

Sub CopyCurrentRow_AC()
Dim nxtRw As Long

'Fill Rtg Base cells with data from ActiveCell Row in Base sheet
    With Sheets("Rtg Base")
      nxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(nxtRw, 1) = Cells(ActiveCell.Row, 1)
        .Cells(nxtRw, 2) = Cells(ActiveCell.Row, 2)
        .Cells(nxtRw, 3) = Cells(ActiveCell.Row, 8)
    End With
End Sub

Sub CopyCurrentRow_Sel()
Dim nxtRw As Long

'Fill Rtg Base cells with data from Selection Row in Base sheet
    With Sheets("Rtg Base")
      nxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(nxtRw, 1) = Cells(Selection.Row, 1)
        .Cells(nxtRw, 2) = Cells(Selection.Row, 2)
        .Cells(nxtRw, 3) = Cells(Selection.Row, 8)
    End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#11
March 7, 2018 at 10:28:42
both works great!
I just need to adjust it add the If condition - to copy only the rows that have the column F filled as "Yes" on the Base worksheet.

in any row (excluding the header), if Column F = Yes, then copy columns A, B and H of Base worksheet to the columns A, B and C of Rgt Base worksheet .

it can be a loop function to, every time that the macro is ran, check the condition on all the Base Worksheet and re-copy (or override) the Rtg Base worksheet (discarding the headers, which is row 1 of base worksheet and rows 1 and 2 of Rtg Base worksheet

would you think that this solution would work better?

kind regards


Report •

#12
March 7, 2018 at 15:18:36
Well, you certainly have changed your original requirements. It's a good thing you are not being charged for these solutions.

Try this:

Sub CopyYesData()
Dim nxtRw As Long
Dim startAddress As String
Dim y As Range

'Clear old data from Rtg Base
   Sheets("Rtg Base").Range("A3:C" & Rows.Count).ClearContents
   
'Initialize next row variable
   nxtRw = 2
   
'Find each occurance of Yes in Base Column F
'Set cell values for each Yes row
   With Sheets("Base").Columns(6)
     Set y = .Find("Yes", lookat:=xlWhole, after:=Range("F1"))
       If Not y Is Nothing Then
          startAddress = y.Address
         Do
           nxtRw = nxtRw + 1
             Sheets("Rtg Base").Cells(nxtRw, 1) = Sheets("Base").Cells(y.Row, 1)
             Sheets("Rtg Base").Cells(nxtRw, 2) = Sheets("Base").Cells(y.Row, 2)
             Sheets("Rtg Base").Cells(nxtRw, 3) = Sheets("Base").Cells(y.Row, 8)
               Set y = .FindNext(y)
         Loop While Not y Is Nothing And startAddress <> y.Address
       End If
   End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#13
March 8, 2018 at 05:25:29
it works perfectly DerbyDad03, thank you very very much for your time and efforts.

By the way - do you have (or recommend) any excel courses? i would happily attend/buy any material from you - you are very didactic!

kind regards
Lucca


Report •

#14
March 8, 2018 at 06:39:23
Sorry, I have no learning recommendations other than the various Excel forums and Google searches. I am basically self-taught via those methods.

The vast majority of my learning over the years has come by doing the research required to answer questions in this (and other) forums. Quite often I run into situations/requirements that I am not familiar with, so I do some searches, adapt what I find, and post a solution.

Now that my dirty little secret is out, maybe my workload will be reduced. ;-)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#15
March 8, 2018 at 06:48:04
I sincerely admire you, DerbyDad03 - you are a very altruist and skilled person.
Thanks again
kind regards

Report •

#16
March 8, 2018 at 12:04:01
Hello DerbyDad03 - I am truly sorry to bother you again with this - and I am willing to reward your efforts (maybe with criptocurrencies? :-) )

After running it a few times, it appeared points that I did not preview

the intention is to shadow all the modifications on the Base worksheet, but keeping it i)formatted and ii)adding/removing blank lines.

the reason why I need the addition of new lines is that, on Rtg Base, 2 rows after the last name (which was flagged as'Yes' at Base worksheet), there is a cell/formula with 'Last updated on' - so I cannot override this row

The steps of what I am looking for are

Filter Yes at column H (Base worksheet):
Compare entries of Base worksheet (already filtered for Yes flag) with Rtg Base worksheet

If all the entries Yes on Base worksheet are already appearing on Rtg Base - do nothing

If there is any entry that was removed from Base but is still showing in Rtg Base, delete the row of Rtg Base

If there is an addition to the Base, add a new line on the last row and copy columns A, B, C and J keeping the format of the previous row



Report •

#17
March 8, 2018 at 13:10:04
Once again, I have to bring up my point related changing your requirements. This latest request is so far removed from your original request that I basically have to start over and rewrite most of the code.

You should keep in mind that the vast majority of the people that answer questions in Help forums do it for free and do it for fun. Trust me when I tell you that it is not fun to answer the question that was asked, then have the question change, answer that one, then have it change again, and yet again.

You even marked this thread as Solved and said "it works perfectly". I'm not sure how you managed to miss such major requirements such as checking for the existence matching data, deleting rows, formatting cells, etc. Heck, you even changed the Columns that need to be copied.

I'll consider rewriting the code to address your new - and completely different requirements - but don't expect as prompt a response as you have been getting.

message edited by DerbyDad03


Report •

Ask Question