Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Insert row/copy based on criteria
I need a macro
that will insert a number of rows based on the number of months between a start and end date and then copy the information in the above row to the new rows. The records are over 8,350.. so ill also need some idea how to get it to stop when it fills the worksheet so i can transfer those into another workbook |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Insert row/copy based on criteria
What version of Excel are you using?
"Katerinia" wrote in message ... I need a macro that will insert a number of rows based on the number of months between a start and end date and then copy the information in the above row to the new rows. The records are over 8,350.. so ill also need some idea how to get it to stop when it fills the worksheet so i can transfer those into another workbook |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Insert row/copy based on criteria
If you have xl2007 or later, you probably do not have to worry about the
number of rows, but I included a cut off just in case. Sub standard() Dim c As Range, rng As Range, a As Long Dim sh As Worksheet, lr As Long Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:A" & lr) For Each c In rng If Not c Is Nothing Then a = DateDiff("m", Range("A" & c.Row).Value, Range("B" & c.Row).Value) If a 1 Then c.Offset(1, 0).Resize(a, 1).EntireRow.Insert End If If c.Row = 65520 Then MsgBox "Less than 16 rows available, Procedure will teminate" Exit Sub End If End If Next End Sub "Katerinia" wrote in message ... I need a macro that will insert a number of rows based on the number of months between a start and end date and then copy the information in the above row to the new rows. The records are over 8,350.. so ill also need some idea how to get it to stop when it fills the worksheet so i can transfer those into another workbook |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help - Insert row/copy based on criteria
P.S.
If your start date is not in column A and your end date is not in column B then you will need to change the column reference in this line: Set rng = sh.Range("A2:A" & lr) And this line: a = DateDiff("m", Range("A" & c.Row).Value, Range("B" & c.Row).Value) The code was based on the assumption that col A contains the start Date and Col B contains the end date. The DateDiff function requires the earlier date to be the argument before the later date or you will get a negative result and the code will fail. "JLGWhiz" wrote in message ... If you have xl2007 or later, you probably do not have to worry about the number of rows, but I included a cut off just in case. Sub standard() Dim c As Range, rng As Range, a As Long Dim sh As Worksheet, lr As Long Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:A" & lr) For Each c In rng If Not c Is Nothing Then a = DateDiff("m", Range("A" & c.Row).Value, Range("B" & c.Row).Value) If a 1 Then c.Offset(1, 0).Resize(a, 1).EntireRow.Insert End If If c.Row = 65520 Then MsgBox "Less than 16 rows available, Procedure will teminate" Exit Sub End If End If Next End Sub "Katerinia" wrote in message ... I need a macro that will insert a number of rows based on the number of months between a start and end date and then copy the information in the above row to the new rows. The records are over 8,350.. so ill also need some idea how to get it to stop when it fills the worksheet so i can transfer those into another workbook |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Macro- Insert rows based on dates and copy info from that row | Excel Discussion (Misc queries) | |||
Excel macro to copy row from one tab to another based on certain criteria | Excel Programming | |||
insert rows based on criteria | Excel Discussion (Misc queries) | |||
Create a Macro to Insert a number into a column based on criteria | Excel Programming | |||
Macro or formula to copy data based on criteria | Excel Programming |