This is for a pro - Automatically Insert a Number of Rows Starting Down a Specified Row

Posted by Oeloel on August 05, 2000 7:04 AM

Hi,

Suppose you want to Insert 24 rows not inmetiately after the row where the active cell is but below (down another 24 rows) where you want to add a person's data. Each person should have 24 rows for the user to enter data corresponding to 24 weeks. I want to not only insert those 24 rows but also automatically have those rows be formated with the formating of the preceeding 24 rows that belongs to the previous person's data where the active cell was. I also want all the formaulas of thaose rows be added to the inserted 24 rows. Is my request easy to understand?

Posted by Celia on August 05, 0100 6:53 PM

Oeloel

Try this :-

Sub InsertNewSet()
Dim startSet As Range, newSet As Range, cell As Range
Set startSet = Range(Selection.EntireRow, Selection.EntireRow.Offset(23, 0))
Set newSet = startSet.Offset(24, 0)
newSet.Insert Shift:=xlDown
Set newSet = startSet.Offset(24, 0)
startSet.Copy
newSet.PasteSpecial xlPasteFormats
For Each cell In Intersect(ActiveSheet.UsedRange, startSet)
If cell.HasFormula Then cell.Copy cell.Offset(24, 0)
Next
Selection(1, 1).Select
End Sub

Before running the above, make sure that the selected cell(or cells) is in the first row of the data set below which the new set is to be inserted.

Celia

Posted by Ivan Moala on August 05, 0100 6:57 PM

No one has answered so.....

Try this, you didn't specify a range so then routine
assumes a column = A;

Sub InsertRws_Copy()
Dim RgToCpy As Range
Dim Rws As Integer

Rws = 24 'Number of rows to insert CHANGE THIS FIG to insert more

If ActiveCell.Row / Rws &LT; 1 Or ActiveCell.Row / Rws = 1 Then
MsgBox "Wrong row!"
End
End If

Set RgToCpy = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-Rws, 0))
Range(ActiveCell, ActiveCell.Offset(Rws - 1, 0)).Select
Selection.EntireRow.Insert
RgToCpy.Copy Destination:=Selection
Application.CutCopyMode = False
End Sub

The activecell has to be the one below your last
data row eg you specified 24 so row 25 should be selected.

HTH

Ivan

Posted by Celia on August 05, 0100 8:00 PM

Here's an improved version. Like Ivan's, it includes a check to make sure the selected cell is in a correct row before the macro is run :-

Sub InsertNewSet()
Dim firstRw As Integer, Rws As Integer, startSet As Range, newSet As Range, cell As Range

firstRw = 1 'The row number of the first row of the first data set _
on the worksheet. Change the number to match the workseet

Rws = 24 'Number of rows per set
Set startSet = Range(Selection.EntireRow, Selection.EntireRow.Offset(Rws - 1, 0))
Set newSet = startSet.Offset(Rws, 0)

If (ActiveCell.Row + (Rws - firstRw)) / Rws - Int((ActiveCell.Row + (Rws - firstRw)) / Rws) &LT;&GT; 0 Then
MsgBox "Wrong row!"
End
End If

newSet.Insert
Set newSet = startSet.Offset(Rws, 0)
startSet.Copy
newSet.PasteSpecial xlPasteFormats
For Each cell In Intersect(ActiveSheet.UsedRange, startSet)
If cell.HasFormula Then cell.Copy cell.Offset(Rws, 0)
Next
Selection(1, 1).Select
End Sub

Celia