I have a macro that will copy and paste more lines in a section of m
spreadsheet, the problem I am running into now is when the macro i
exectuted and the lines are added, the SUM of lines is not working.

I am calculating the sum of certain cells

SUM(G10, G7, G4, G1)

when I insert my new lines, I want the formula to now include

SUM(G13, G10, G7, G4, G1)

this is NOT what it is doing.
When I insert the new lines the formula switches to

SUM(G13, G7, G4, G1)

What is happening? The code I have for copy/paste lines is


Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells.Find("Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, LenB(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.Insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub

Thanks :confused

--
tanyhar
-----------------------------------------------------------------------
tanyhart's Profile: http://www.excelforum.com/member.php...fo&userid=3514
View this thread: http://www.excelforum.com/showthread.php?threadid=55114

assume the formula is in A1

Dim rng as Range
set rng = Range("A1").DirectPrecedents
Rows(13).Insert
set rng = union(cells(13,"G"), rng)
Range("A1").Formula = "=sum(" & rng.address(0,0) & ")"

--
Regards,
Tom Ogilvy


"tanyhart" wrote in
message ...

I have a macro that will copy and paste more lines in a section of my
spreadsheet, the problem I am running into now is when the macro is
exectuted and the lines are added, the SUM of lines is not working.

I am calculating the sum of certain cells

SUM(G10, G7, G4, G1)

when I insert my new lines, I want the formula to now include

SUM(G13, G10, G7, G4, G1)

this is NOT what it is doing.
When I insert the new lines the formula switches to

SUM(G13, G7, G4, G1)

What is happening? The code I have for copy/paste lines is


Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells.Find("Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, LenB(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
EntireRow.Copy
EntireRow.Insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub

Thanks


--
tanyhart
------------------------------------------------------------------------
tanyhart's Profile:
http://www.excelforum.com/member.php...o&userid=35148
View this thread: http://www.excelforum.com/showthread...hreadid=551147

Exchange lots of mouse moves and clicks for a single key press!
Significantly improve working speed by using shortcuts (hot keys).
Avoid Repetitive Strain Injury!

EnergyKey http://www30.webSamba.com/SmartStudio