Solved Using VBA to insert row and paste/modify data in excel

September 12, 2013 at 20:10:41
Specs: Windows 7
Hi all, I am wondering if it's possible to insert a row based on data then copy, paste, modify this data, For example if 3H = "Example" then insert a new row underneath and copy MOST of the cells from row 3 into the (new) row 4, with a few modifications on data to some. ie, Dates, and a few text strings using the MID() function. Is it possible to do this?

See More: Using VBA to insert row and paste/modify data in excel

Report •

✔ Best Answer
September 16, 2013 at 14:53:45
BTW, while this code will run "automatically when 'Renewed' is selected from the drop down box, it is not gong to update all open workbooks at once. If fact, I'm not sure what will happen if one user causes a row to be added at e.g. Row 10 at the same time another user causes a row to be added at e.g. Row 10 in their open spreadsheet.

Excel can resolve conflicts in a shared workbook to some extent, but it's not going to know that one user's Row 11 actually has to be Row 12 because 2 people add a Row at the same time.

You are going to have to do some testing to see what happens if 2 or more people cause a row to be inserted at relatively the same time.

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

message edited by DerbyDad03



#1
September 13, 2013 at 09:56:42
Yes, it is possible to use VBA to do what you want, but we would need some more details related "copy MOST of the cells", " a few modifications", etc.

For example, this code will search Column H for the string Example. Whenever it is found, it will copy the row to the row below, delete a couple of cells (leaving "most of the cells") and then use the MID function to modify the contents of one cell:

Sub CopyModify()
'Search Column H for "Example"
With Worksheets(3).Range("H:H")
    Set c = .Find("Example", LookIn:=xlValues, LookAt:=xlWhole)
'If found...
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
'Copy the row to the row below
            c.EntireRow.Copy
            Rows(c.Row + 1).Insert Shift:=xlDown
'Change the contents of the cell in Column H so it woon't be found again
            Cells(c.Row + 1, c.Column) = "New" & Cells(c.Row + 1, c.Column)
'Clear cells in Column B and G
            Cells(c.Row + 1, 2).ClearContents
            Cells(c.Row + 1, 7).ClearContents
'Extract a couple of characters from value in Column F
            Cells(c.Row + 1, 6) = Mid(Cells(c.Row + 1, 6), 3, 2)
'Find the next occurrence of "Example"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
'Replace NewExample with Example to return original values to Column H
   Columns("H").Replace What:="NewExample", Replacement:="Example", LookAt:=xlWhole
End Sub

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


Report •

#2
September 13, 2013 at 13:45:58
Thanks for the reply. Basically, I need it so that when the term 'Renewed' is entered in Column K from a dropdown box, Columns B, C, D, G, J are copied, columns E and F are left empty in new row, Column K will read 'Open' from the dropdown box. Column H and Column I will be the date, but with 28 days added to it. Column A would be the same as if the following formula was manually entered in it:
=IF(MID(A11,11,1)="",MID(A11,1,10)&" (Rev 1)",IF(MID(A11,18,1)=")",MID(A11,1,16)&MID(A11,17,1)+1&")",MID(A11,1,16)&MID(A11,17,2)+1&")"))
assuming the 'old' row was row 10.

Thanks again for any assistance!


Report •

#3
September 15, 2013 at 08:17:50
Some things are not clear to me.

Column H and Column I will be the date, but with 28 days added to it.

What date? The current date? The date that was in the original row? Something else?

Your MID formula for Column A

If the old row was 10 then the new row would be 11. If you enter a formula referencing A11 in A11, you will get a circular reference.

Please clarify these issues. Thanks.

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


Report •

Related Solutions

#4
September 15, 2013 at 13:39:21
There are 2 dates in the original row: Columns H and I. The new column H would be the day after the old column I. The new column I would be 29 days after the new column H.

The mid formula is in the new row, referring to the previous row.


Report •

#5
September 15, 2013 at 21:11:15
So "assuming the 'old' row was 10” the formula should reference A10, not A11, right?

As far as your dates, you do realize that what you asked for in your latest response is very different than what you asked for in Response #2, don't you?

First you asked that 28 be added to the dates, now you are asking that 1 be added to one date and then 29 be added to that date. Those two requests are so different that I want to be sure I understand your requirements.

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


Report •

#6
September 15, 2013 at 21:23:47
Apologies for the discrepancy.

There is a row. It could be any row, 10, 11, etc. This row has data in it, which is all manually entered. The macro needs to insert a new row below whichever row has 'Renewed' selected in Column K. The new row will then automatically enter data in it. Column A of the new row will have data entered into it, as if that formula had been used, where the reference in that formula is of the old row. The date manipulations in the new row are of the old row, where new H is a day after old I. and new I is 29 days after new H (ie: 30 days after old I).

The spreadsheet is going to be used by multiple people in multiple locations in a work setting, so if possible, it would be good to somehow make it so that the macro runs automatically when 'Renewed' is selected from the drop down box I have in Column K.

Thanks again.


Report •

#7
September 16, 2013 at 13:11:41
I think this code does what you asked for. Please refer to the comments I have included in the code and see if they match your requirements.

Since I don't know what the "Before & After" for Column A should look like, I simply let the code place the MID formula in the cell and let Excel process the text in the cell above it. The MID function could be processed within the VBA code itself and then the resulting text string could be placed in Column A, but I have no way of testing that since I don't know what your original data looks like or what it should look like after the MID function. Thus, I let VBA put the formula that you wrote in the new Row of Column A.

Right click the Sheet Tab for the sheet you want this code to run in and choose View Code. Paste the code into the pane that opens and test it by using your Column K Drop Downs.

A few words of caution first:

Since you mentioned that multiple users will be using this worksheet, you should be aware that anyone who wants to use the code will need to have macros enabled within their individual Excel application. This is not something you can "force" them to do and you can't force a macro to run on a system that does not have macros enabled. That would be a security issue and is thus prevented.

In addition, it would behoove you to hide and protect the VBA code so that the users can't see it, or more specifically, can't edit/delete it.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if only the DropDown in Column K was changed and if "Renewed" was chosen
   If Target.Cells.Count = 1 And Target.Column = 11 And Target = "Renewed" Then
'If Yes, turn off Events...
       Application.EnableEvents = False
'Copy row, insert below
          With Cells(Target.Row, 11)
           .EntireRow.Copy
           .EntireRow.Insert shift:=xlDown
          End With
'Set variable for new row (using Target.Row confuses VBA)
           newRow = Target.Row
'Place formula in Column A, refering to original Row
             Cells(newRow, 1) = _
                      "=IF(MID(R[-1]C,11,1)="""",MID(R[-1]C,1,10)&"" " _
                      & "(Rev 1)"",IF(MID(R[-1]C,18,1)="")""," _
                      & "MID(R[-1]C,1,16)& MID(R[-1]C,17,1)+1&"")""," _
                      & "MID(R[-1]C,1,16)&MID(R[-1]C,17,2)+1&"")""))"
'Clear Columns E & F
             Range(Cells(newRow, 6), Cells(newRow, 7)).ClearContents
'Put new dates in Columns H & I
             Cells(newRow, 8) = Cells(newRow - 1, 8) + 1
             Cells(newRow, 9) = Cells(newRow, 8) + 28
'Set new Drop Down to "Open"
             Cells(newRow, 11) = "Open"
'Re-enable Events
       Application.EnableEvents = True
   End If
End Sub

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


Report •

#8
September 16, 2013 at 14:53:45
✔ Best Answer
BTW, while this code will run "automatically when 'Renewed' is selected from the drop down box, it is not gong to update all open workbooks at once. If fact, I'm not sure what will happen if one user causes a row to be added at e.g. Row 10 at the same time another user causes a row to be added at e.g. Row 10 in their open spreadsheet.

Excel can resolve conflicts in a shared workbook to some extent, but it's not going to know that one user's Row 11 actually has to be Row 12 because 2 people add a Row at the same time.

You are going to have to do some testing to see what happens if 2 or more people cause a row to be inserted at relatively the same time.

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

message edited by DerbyDad03


Report •

#9
September 19, 2013 at 21:19:22
Thanks very much. That works great!

Report •

#10
September 25, 2013 at 19:49:28
I have been using this a bit, and it works exactly as required except for one thing.

Whenever I try to delete a ROW later on, it comes up with an error which states:

Run time error '13':

Type mismatch

I don't mind that this happens because I just need to click 'End', but because this is being used by multiple users, I am trying to "idiot-proof" the spreadsheet, and don't like the idea that someone might click 'Debug' and then accidentally mess with the code.

Any solution you may have will be much appreciated, and thanks once again for the initial code.


Report •

#11
September 25, 2013 at 20:22:25
Try this version. I moved the instruction that determines how many cells have been changed onto it's own line. That should solve the problem.

Let me know...

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if only the DropDown in Column K was changed and if "Renewed" was chosen
   If Target.Cells.Count = 1 Then
    If Target.Column = 11 And Target = "Renewed" Then
'If Yes, turn off Events...
       Application.EnableEvents = False
'Copy row, insert below
          With Cells(Target.Row, 11)
           .EntireRow.Copy
           .EntireRow.Insert shift:=xlDown
          End With
'Set variable for new row (using Target.Row confuses VBA)
           newRow = Target.Row
'Place formula in Column A, refering to original Row
             Cells(newRow, 1) = _
                      "=IF(MID(R[-1]C,11,1)="""",MID(R[-1]C,1,10)&"" " _
                      & "(Rev 1)"",IF(MID(R[-1]C,18,1)="")""," _
                      & "MID(R[-1]C,1,16)& MID(R[-1]C,17,1)+1&"")""," _
                      & "MID(R[-1]C,1,16)&MID(R[-1]C,17,2)+1&"")""))"
'Clear Columns E & F
             Range(Cells(newRow, 6), Cells(newRow, 7)).ClearContents
'Put new dates in Columns H & I
             Cells(newRow, 8) = Cells(newRow - 1, 8) + 1
             Cells(newRow, 9) = Cells(newRow, 8) + 28
'Set new Drop Down to "Open"
             Cells(newRow, 11) = "Open"
'Re-enable Events
       Application.EnableEvents = True
    End If
   End If
End Sub

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


Report •

#12
September 25, 2013 at 20:29:35
Perfect! Genius!

Thankyou very much.


Report •

Ask Question