Solved VBA to copy and insert rows and change some values

December 27, 2013 at 08:29:33
Specs: Windows 7
Hi, I'm looking for a macro that will insert and copy rows dependant on certain criteria:
Column 4 cannot exceed 50 days, so I need a macro that looks at the value in Col 4 and if more than 50 will calculate the # of additional rows required and copy and insert the data. For instance if D were 200, the macro would copy and insert 3 more lines (total of 4 rows) and the value in ALL rows Col D would be 50. If D were 201, 4 rows would be copied and inserted (total of 5 rows) 4 of those would have a value of 50 and the last row a value of 1. In addition, the date would DECREASE by one day for inserted row. So in the first example row 1 would have the dates as 31/12/2013, 30/12/2013, 29/12/2013, 28/12/2013 in the for rows.
Before Macro:
Emp# Name End_Date Days
23 James, Jim 12/31/2013 15
123456 Smith, John 12/31/2013 50
234564 Jones, Cathy 12/31/2013 165
45824 Doe, Manny 12/31/2013 322

After macro:
Emp# Name End_Date Days
23 James, Jim 12/31/2013 15
123456 Smith, John 12/31/2013 50
234564 Jones, Cathy 12/31/2013 50
234564 Jones, Cathy 12/30/2013 50
234564 Jones, Cathy 12/29/2013 50
234564 Jones, Cathy 12/28/2013 15
45824 Doe, Manny 12/31/2013 50
45824 Doe, Manny 12/30/2013 50
45824 Doe, Manny 12/29/2013 50
45824 Doe, Manny 12/28/2013 50
45824 Doe, Manny 12/27/2013 50
45824 Doe, Manny 12/26/2013 50
45824 Doe, Manny 12/25/2013 22

Thanks for looking at this


See More: VBA to copy and insert rows and change some values

Report •


#1
December 27, 2013 at 21:00:28
This is certainly doable, but I won't have access to a computer with Excel for a couple of days. If no one else offers a solution by the time I get back to civilization, I'll work on this then.

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


Report •

#2
December 30, 2013 at 07:50:25
✔ Best Answer
First, a posting tip:

Before posting any more example data in this forum, please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Thanks!

As for your question, I pretty sure that this macro does what you want it to do.

The code is based on the assumption that your orginal data is in Sheet1, Columns A:D.

As written, the code writes the output to Sheet2, so if you have anything in Sheet2, it is going to be overwritten. You can either change the code to write the data to a different sheet, or you can insert a blank Sheet2.

Let me know if you have any questions.

Sub Fiftymax()
'Clear Sheet2, Copy Row 1 form Sheet1
    Sheets(2).Cells.ClearContents
    Sheets(1).Rows(1).EntireRow.Copy _
       Destination:=Sheets(2).Range("A1")
'Determine last row with data in Sheet1
       lastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through data in Sheet1
   For Each numDays In Sheets(1).Range("D2:D" & lastRow)
'If Days ,= 50, just copy Row to next blank Row in Sheet2
      If numDays <= 50 Then
        nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
          numDays.EntireRow.Copy _
             Destination:=Sheets(2).Range("A" & nxtRow)
      Else
'If Days > 50, Determine how many new rows will be needed
        numRows = Int(numDays / 50) + 1
'Copy Row to next blank row in Sheet2 multiple times
          For newRow = 1 To numRows
             nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
               numDays.EntireRow.Copy _
                   Destination:=Sheets(2).Range("A" & nxtRow)
'Set Columns D to 50
                 Sheets(2).Range("D" & nxtRow) = 50
                  If newRow > 1 Then
'Decrement Date Afer first row is copied
                     Sheets(2).Range("C" & nxtRow) = _
                         Sheets(2).Range("C" & nxtRow - 1) - 1
'Determine remainder Days for final copied row
                       If newRow = numRows Then
                         Sheets(2).Range("D" & nxtRow) = numDays Mod 50
                       End If
                  End If
          Next
      End If
     Next
End Sub

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


Report •

#3
December 30, 2013 at 09:37:19
That is PERFECT! This will save hours if not days of tedious cutting and pasting. Thanks so much - and I'll be sure to follow your psoting tips in the future:-)
B

Report •

Related Solutions

#4
January 15, 2014 at 14:08:29
Uh oh.... I didn't anticipate Col 4 not having only whole numbers so while the MOD function works on the whole numbers it is rounding any fractional remainders to whole. i.e.someone with 110.5 days should be broken down to 50, 50 and 10.5, but the MOD returns 11. Suggestions/ideas?

Report •

#5
January 15, 2014 at 15:53:32
Either I never knew this, or I simply forgot it:

In Excel MOD(110.5, 50) is a floating point operation and will return 10.5

In VBA, 110.5 Mod 50 is an integer operation and the 110.5 will be rounded to 110 before the division takes place, thus you will get 10. 110.6 will be rounded to 111 and you'll get 11.

One way to solve this is to force VBA to use the Excel MOD function instead of the VBA Mod operator.

Replace this:

numDays Mod 50

with this:

Evaluate("MOD(" & numDays & ",50)")

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

message edited by DerbyDad03


Report •

#6
January 16, 2014 at 08:08:23
Once again, AWESOME!. I did find the evaluate function but not being familiar with VBA syntax couldn't get it to work. But I'm learning :-)
Thanks again for help.

Report •


Ask Question