Insert a row using a macro

April 15, 2009 at 10:48:37
Specs: Windows XP
I need a macro that will identify if a value is entered into a specific cell, then that whole row needs to be copied.

Ie: if F2 contains data, insert line and copy row two.

I found the following macro which is specific to inserting a row and copying formulas only. I need to also copy the constants also.
Excel 2003
Any suggestions?

See More: Insert a row using a macro

Report •

April 15, 2009 at 12:25:12
Right click the sheet tab that contains the F2 you will be changing and paste in this code.

Everytime F2 is changed, a copy of Row 2 will be inserted right below it.

(You didn't say where your "insert" should occur)

Report •

April 15, 2009 at 13:05:45
I am not sure I understand your response. I apologize, I am really only knowledgeable in copy/play macros.

By F2, I was trying to just give an example of a specific cell. I realize now that could also be interpreted to mean that when you hit F2, you see a formula or reference in the cell.

It would be preferable for the lines to be inserted below the line it is copying. I wasn't sure if that could be done, or if it would somehow cause a loop (as the macro moved to that copied row, it would see the data in that cell, then copy the row again, etc).

Will the code I found work for me?

Report •

April 15, 2009 at 16:19:43
Whoops! It looks like I forgot to include the code. I can see why you are confused! you go...

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Target.Address = "$F$2" Then
   Target.Resize(rowsize:=2).Rows(2).EntireRow. _
      Resize(rowsize:=1).Insert Shift:=xlDown
   Application.CutCopyMode = False
  End If

This code will run whenever a change is made to the contents of cell F2.

If you need some type of code to run when other cells are changed, you'll need to be more specific.

The "target" is a cell or range of cells that, when changed, cause the Change event code to run. You could also have code that runs when a specific cell is simply selected, as well as when many other Events occur.

I should add that any code offered in a forum like this should only be a starting point. For example, you say that you are only familiar with copy/play macros. Well, here is the chance for you to broaden your knowledge. Now you know that Event macros exist - marcos that can be triggered by events that occur in the worksheet. Look them up in Excel help or use Google for more info.

Come on back if you need more help.

Report •

Related Solutions

Ask Question