Thread: Inserting rows and copy paste

Hi guys,

I have a set of data that is around a 10000 rows long and it comprises of planned and actual data for production of apples by each base in different countries.
Now I would like to find the differences between the actual and the planned values for all the months I have data for.
The only way I can think of is to insert a new row and introduce formula in the new row to calculate the differences between the actual and planned values.
The problem is that I have 10000 lines of data and obviously I cannot introduce a new row and copy and paste the formula for each of them without going insane!
Can anyone help me on this.

Thanks.

Why not create a new worksheet (tab) to do this?

I found the following post in another forum with a macro which worked perfectly for this purpose.

"Ok. . .So basically what you need is to put a x amount of rows at certain intervals. . .

Try using this code and get back to me if it does not work for you. . .Copy it to a standard Module

When you do tun it, be sure to highlight the range to wish to put intervals in "

Code:

Sub InsertRowsAtIntervals()
'21/07/06 Gareth Lombard
On Error Resume Next
Dim c As Range, i As Long, rwu As Long, rwl As Long
Dim rwc As Long, rwNo As Long, rwCount As Long
If TypeName(Selection) <> "Range" Then Exit Sub
Set c = Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + Selection.Columns.Count - 1))
rwCount = Selection.Rows.Count
rwNo = InputBox("Enter row interval. ", "Insert Rows at Intervals", 1)
rwl = InputBox("How many rows to insert at each interval? ", "Insert Rows at Intervals", 1)
rwu = ActiveCell.Row + rwNo
rwc = rwl + rwNo

For i = 1 To Int(rwCount / rwNo)
Range(Cells(rwu, ActiveCell.Column), Cells(rwu + rwl - 1, ActiveCell.Column)).Select
Selection.EntireRow.Insert
rwu = rwu + rwc
Next
Range(c, Selection).Select

End Sub



The same procedure applies when wanting to add a space in selcted columns. . See code below..

Code:

Sub InsertColumnsAtIntervals()
'27/07/06 Gareth Lombard
On Error Resume Next
Dim c As Range, i As Long, clu As Long, cll As Long
Dim clc As Long, clNo As Long, clCount As Long
If TypeName(Selection) <> "Range" Then Exit Sub
Set c = Range(ActiveCell, Cells(ActiveCell.row + Selection.Rows.Count - 1, ActiveCell.Column))
clCount = Selection.Columns.Count
clNo = InputBox("Enter column interval. ", "Insert Columns at Intervals", 1)
cll = InputBox("How many columns to insert at each interval? ", "Insert Columns at Intervals", 1)
clu = ActiveCell.Column + clNo
clc = cll + clNo

For i = 1 To Int(clCount / clNo)
Range(Cells(ActiveCell.row, clu), Cells(ActiveCell.row, clu + cll - 1)).Select
Selection.EntireColumn.Insert
clu = clu + clc
Next
Range(c, Selection).Select

End Sub

Yes, you can automate the process of adding rows and even copying the formulas for you. I guess my suggestion was to have your variance analysis separate from your data. That, of course, is up to you.

Im sorry, I didnt make myself clear earlier.. I actually wanted to consolidate what I originally had before (Which was in 2 sheets as you have suggested) into 1.. Hence I was looking for this type of solution.

I appreciate your prompt reply however.

Thanks.