Solved Auto-insert new row on different worksheet on condition

December 29, 2016 at 08:34:20
Specs: Windows 7 Enterprise
Hi,

I have a question very similar to another question that was answered in a thread on this site around a year ago.

I found that thread very helpful but my issue has one other part to it so I was hoping to get people's input on how I might solve it as the original thread is now solved and closed.

Here is the thread that was solved:

"I have a spreadsheet with 10 worksheets on it, 1 for each supplier with the same standard layout on each:

Column A: Product Code
Column B: Product Name
Column C: Cost Price
Column D: Sales Prices

I use these individual spreadsheets to import my products to sage. It would make life a lot easier if I had 1 worksheet in this same file with all the information from the others so that I would only have to import 1 file e.g. every time i add a row of information to either one of those 10 supplier sheets it adds a new row with the same information into the master sheet. How would i do this?"

My issue is incredibly similar, the only difference is that I would have a fifth column, let's say Column E, with something like 'Sold' written in it. This column is only ever going to contain a "yes" or "no" value.

I would like to be able to auto-insert a new row on a different worksheet but only when the value of column E is "yes".

If I had 100 entries and 90 of them were "no" and 10 of them were "yes", my aim would be to run the macro and auto-insert the 10 "yes" rows into the different worksheet. If, at a later date, one of the "no" rows was to be updated to a "yes", my aim would be to run the macro again and this time for it to insert/overwrite everything on the different spreadsheet with the original 10 "yes" rows and also the new "yes" row.

Thank you in advance for any help.


See More: Auto-insert new row on different worksheet on condition

Report •

#1
December 29, 2016 at 10:12:19
✔ Best Answer
I'm not sure what you mean by "auto-insert a new row".

"Auto" implies it happens automatically, yet you say that you want to run the macro each time.

"Insert" implies that the row is placed between existing rows, yet you didn't give any indication as to where the row would be inserted except "into the different worksheet".

The "auto" part can be accomplished such that the code can run "automatically" as soon as a cell in Column E has Yes entered into it. That can be done with a Worksheet_Change macro. That macro would not have to be run manually, it would run automatically on the change of the cell to Yes.

By "insert" do you perhaps mean "append? i.e. copy the row to next available row on the "different" sheet? There would be no need to overwrite the existing data. The code would simply determine the next available row and paste the "new row" there.

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


Report •

#2
December 30, 2016 at 01:48:40
Thanks for your reply and for working your way through my inaccurate terminology.

Accomplishing the auto part so that the code can run automatically as soon as a cell in Column E has Yes entered into it would be ideal. Failing that a partially automated solution, based on the solution from the other thread, whereby a triggered macro achieves that same result would also be an acceptable solution.

Yes, 'append' is the correct word in this context not 'insert'. Thanks.

I'll explain the exact process to try and clarify what I'm trying to do.

Let's say I have a spreadsheet with 10 columns (A-J). When the value in Column E is entered as Yes I would like to append selected columns (A,C, E and G, for example) to the first row below the headings in the different worksheet (the other columns B,D,F,H, I and J) don't need to be in the different worksheet. Ideally the rest of the data in that different worksheet that was already there would be pushed down one row so that the data is automatically appended with most recent on top.


Report •

#3
December 30, 2016 at 06:25:20
re: "Yes, 'append' is the correct word in this context not 'insert'."

Actually, "append" is the wrong word. "Append" means to add on to the bottom, i.e. to hang off of, as an appendage. Since you want to insert the row at the top, you were correct in using insert in your original request.

re: "Accomplishing the auto part so that the code can run automatically as soon as a cell in Column E has Yes entered into it would be ideal. Failing that a partially automated solution, based on the solution from the other thread, whereby a triggered macro achieves that same result would also be an acceptable solution."

Running the code "automatically" and "triggering" the code is the same thing. Since I will be suggesting a Worksheet_Change macro, any change to the worksheet will "trigger" the code to run. The first thing the code will do is decide whether the change meets specific conditions (e.g. "Was Yes entered into a cell in Column E?) If the conditions are met, the rest of the instructions will be executed. If not, the code will exit and wait for the next change.

The following code assumes that you have a sheet named Master that will accept the inserted row. If the sheet has a different name, simply edit the instruction that reads With Sheets("Master") and enter the correct sheet name between the quotes.

The code also assumes that you have Column Headers in Row 1. It will therefore paste the copied rows into Row 2. If that is not what you have, change all of the "2"s after the With Sheets("Master") instruction to reflect the row in which you want the paste to occur.

Right click the sheet tab for the sheet in which you want the copy to occur. Choose View Code and paste the following macro in the pane that opens. Enter Yes in Column E and the row should be copied to the Master sheet. You will need to paste a copy of the code into every sheet in which you want the copy to occur. Worksheet_Change macros are specific to the sheet in which they are stored. You should not need a copy of the code in the Master sheet module.

You will need to save the workbook as an xlsm or xlsb file type because it now contains a macro.

If you get any errors or have problems when you try the code, please be specific as to what the error says or what the problem is. Simply telling us that "It doesn't work" or "It gives me an error" doesn't help us help you.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Yes was entered in Column E
 If Target.Cells.Count = 1 Then
  If Target.Column = 5 And Target = "Yes" Then
'Copy Target Row
    Target.EntireRow.Copy
'Paste into Master sheet, Row 2, Delete data in B,D,F,H,I
      With Sheets("Master")
         .Range("A2").Insert shift:=xlDown
         Application.Union(.Range("B2"), .Range("D2"), .Range("F2"), _
                           .Range("H2"), .Range("I2")).ClearContents
      End With
  End If
 End If
End Sub

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


Report •

Related Solutions

#4
January 3, 2017 at 06:43:02
This worked perfectly. Thank you very much for your clear explanation DerbyDad03. I tweaked a couple of bits of it to fit the spreadsheet I'm working with and I worked through each part of the code slowly in an attempt to try and understand what each part means as I'd like to have at least a cursory understanding of it as opposed to just copying and pasting the code. Thanks again.

Report •

#5
January 3, 2017 at 07:10:43
If you want to learn a little more about coding, you might want to review a tutorial that I wrote many years ago. I find the techniques to be extremely useful not just for troubleshooting code that I have written or that I am trying to fix, but also for "reverse engineering" working code that I find on the web.

http://www.computing.net/howtos/sho...

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


Report •

#6
January 3, 2017 at 08:23:08
I'll take a look. Thanks.

Report •

Ask Question