Solved Macro to insert a row based on a value in Excel? Please help

June 5, 2015 at 13:47:21
Specs: Windows 7

I have a similar problem and tried adapting this with no luck.

If there is a 1 in column F, I need the full row to repeat, including the functions within in.

Here's an example:
AM33SAS-F.bmp M SAS 33 0 0
AF05SAS-F.bmp F SAS 05 1 AF05SAS-F.bmp <-- This row must repeat.

I need info on how to input this as well. I have been trying to create a Macro, but I'm not sure I did it properly. The videos online don't help. With the above script, I have received errors for the name I gave the Macro, End Sub Expected, and even when I don't get an error nothing happens. I need the macro to apply to specific sheets. Please help!

Here is my adapted script:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHnd
'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub
'Determine if the changed cell is in Column F and is a 1
If Target.Column = 6 Then
If Target = "1" Then
'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'Copy & Insert changed Row, Clear dotted lines
Range("A" & Target.Row + 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End If
End If
'Re-enable event
Application.EnableEvents = True
End Sub

See More: Macro to insert a row based on a value in Excel? Please help

Report •

June 6, 2015 at 01:40:48
Just so you know that you aren't being ignored, I've read your post and will work on something a bid later. I won't have access to Excel until later today.

In the the meantime...

1 - The code you posted is known as an "event macro". It is designed to fire when an event occurs, in this case, a change to the spreadsheet. Specifically, it does its job as soon as a 1 is manually entered in Column F by the user.

I am assuming that you want to loop through an existing data set and copy each line where a 1 is already present in Column F. Please confirm this.

2 - Please click on the following line and read the instructions on how to post VBA code in this forum. Thanks.

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

Report •

June 7, 2015 at 06:43:08
✔ Best Answer
Assuming you are running this code against existing data, give this a try:

Sub CopyBasedOn_F()
Dim lastRw, nxtRw

'Disable ScreenUpdating for efficiency
 Application.ScreenUpdating = False

'Determine last row with data in Column F
   lastRw = Range("F" & Rows.Count).End(xlUp).Row

'Loop through data in reverse order
     For nxtRw = lastRw To 1 Step -1
      If Range("F" & nxtRw) = 1 Then

'If a 1 is found in Column F, insert/copy row
            Range("F" & nxtRw).EntireRow.Copy
               Range("A" & nxtRw + 1).Insert Shift:=xlDown
       End If

'Clear copy indicator
    Application.CutCopyMode = False
End Sub

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

Report •

June 7, 2015 at 09:19:12
Hi there,

Just to let you know, I've seen your reply. I haven't been near Excel yet either, so I haven't been able to try it yet. Thanks in advance, though!!! I'll let you know as soon as I've tried it out. :)

Report •

Related Solutions

June 13, 2015 at 14:01:57
IT WORKS!!! Thank you SO much!!! My entire lab owes you their gratitude!

Report •

June 13, 2015 at 20:12:21
I did my part, now here's some work for you.

Click on the following the link and read the tutorial while practicing the debugging techniques on the working code. The debugging techniques described in the tutorial are not just for fixing code that doesn't work, they are also great for figuring out how working code does what it does. There's a lot of great code available on the internet, but before you can modify for your own use, you need to understand how it works. "Debugging" code that is working properly is a great way to learn how to modify existing code and eventually learn to write your own from scratch.

message edited by DerbyDad03

Report •

Ask Question