Keeping formulas on an insert

[CellShocked]

Hey folks.

I have a macro where I select a whole row, and the insert it at a
location. There are formulas above and below the insertion.
the copied row has the formula, but it fails to carry on the paste.

I think maybe all I need to do is add some declaration in the insert
line to grab it as well or...

Anyway, here is the code snip.

Sub ProgAdd()
'
' ProgAdd Macro
' Insert a line within a named range for the Program Listing

Sheets("Programs").Select
Range("WOLineAdd").Select
Selection.Copy
Range("NewProgLine").Select
Selection.Insert Shift:=xlDown
End Sub

The "NewProgLine" select an entire row at the bottom of the 'table'.
This makes the insertion within a previously defined range.

One of the cells has a formula in it.

So, I guess what I really want is a "copy" and then an "Insert copied
Cells" coding to carry everything that copied row contains.

 

[GS]

I mostly use context-sensitive menus with multi-sheet projects so only
the actions that the active sheet supports are available/enabled. That
said, I have a common 'AddRows' procedure that's available on the
right-click popup menu of some sheets. This has 3 menuitems...

"Add rows above here"
"Add rows below here"
"Add more rows"

...where the 1st 2 menuitems are relative to the active cell, and the
last menuitem inserts rows at the bottom of the input area.

The row being inserted are predesigned for formatting/formulas and
are defined name ranges stored (hidden) at the top of the worksheet in
my "ProgRows" area. (I do same for cols to the left in my "ProgCols"
area) Here's my procedure...

Sub AddRows()
' Inserts a specified number of rows at the location specified in the
ActionControl.Tag property

Const sSource$ = "AddRows()"

Dim vRowCount As Variant, lPos&
Const sMsg$ = "Enter the number of rows to insert."

'Evaluate user input
vRowCount = 1 '//the default
On Error Resume Next
vRowCount = InputBox(sMsg, gsAPP_NAME, Default:=1)
If vRowCount = "" Then Exit Sub '//user cancels
If Not Err = 0 Or Not IsNumeric(vRowCount) Then _
Call NotifyInvalidInput: Exit Sub

'Determine the number of rows
Select Case vRowCount
Case Is >= 1: vRowCount = vRowCount
Case Else: Call NotifyInvalidInput: Exit Sub
End Select 'vRowCount

'Get the position to insert
Select Case CommandBars.ActionControl.Tag
Case "Above": lPos = ActiveCell.Row
Case "Below": lPos = ActiveCell.Row + 1
Case "Add": lPos = Cells(Range("InputArea").Rows.Count, 1).Row + 1
End Select

'Insert the rows
EnableFastCode True
ActiveSheet.Unprotect PWRD
With Range("BlankTransaction")
.EntireRow.Hidden = False
.Copy: Cells(lPos, 1).Resize(vRowCount).Insert Shift:=xlDown
.EntireRow.Hidden = True
End With
Application.CutCopyMode = False '//turn off phantom selection

'Reset the scroll area to include the new rows
Call SetupUI(ActiveSheet): Call EnableFastCode(False): Call wsProtect
End Sub 'AddRows

...wherein you'll note other things going on that relate to the
insertion of new rows on protected sheets. Ignore that code and focus
on the copy/insert code inside the With...End With block. My
"BlankTransaction" range is an entire row (or rows depending on the
sheet context), as are all my ProgRows. (same for ProgCols)

All the predesigned formatting/formulas follow intact.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion