Hello,
I know that something relative to this has been posted before but I
need to get a little more specific in this newsgroup in hopes that I
can get some help. Please keep in mind that I am not a developer, nor
an expert with Excel.

I have (somewhat unsuccessfully) written an excel macro to Insert a
black row while copying the formula and formatting from the row above
it. The macro is assigned to a button that....when the button is hit
.....there is one row added at the row of the "Active Cell".

Problem: Whenever I insert a row, instead of the macro inserting a
blank row containing only the formula from the row above, it will copy
the data along with the formula...which is not what I'm looking
for....

Here is the snippet of code that goes with my InsertRow macro:
Sub InsertCopiedRow()
ActiveSheet.Unprotect "****"
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveSheet.Protect "****"

End Sub

I have scoured newsgroups and MSVP Excel pages....and it seems like
everyone is pointing at doing some sort of changes with the formulas
AND the actual macro.....especially with some sort of OFFSET function.
I am open to making this work, but I cannot figure out how to insert
OFFSET properly into my formulas/macros if necessary....

Please see to get a visual of the worksheet:
http://php.indiana.edu/~cawagner/macro.jpg

Any help would be greatly appreciated......

Roger

This appears to be a template that is fixed as far as vertical size is
concerned. In other words, do you really want to insert a new row and
possibly cause the printout to wrap onto a second page, or do you simply
want to have the formulas show values when you start entering data on a new
row?

In this situation, I usually enter formulas in ALL of the cells (rows 8 thru
15 in your JPG screenshot), even though most of them will not have data. Set
up the formulas so that any values are 0 (or some other sentinel value) if
column $A or $B is blank. So, the formula in cell $L$10 would be:
=IF(ISBLANK(A10),0,0.375*K10)

Then format the values so that they do not show when the value is 0. See
the "Create a custom number format" topic in Excel Help. The format for this
example would be something like the following (notice the 2 semi-colons next
to each other near the end of the line for the 0 format):
#,##0.00;[Red] #,##0.00;;@

This completely eliminates the need to write any macros to add or delete a
line, which can get very messy when you have a "fixed size" form like this
to populate.
--
Regards,
Bill


"iltf124 no spam @hotmail.com" wrote in message
m...
Hello,
I know that something relative to this has been posted before but I
need to get a little more specific in this newsgroup in hopes that I
can get some help. Please keep in mind that I am not a developer, nor
an expert with Excel.

I have (somewhat unsuccessfully) written an excel macro to Insert a
black row while copying the formula and formatting from the row above
it. The macro is assigned to a button that....when the button is hit
....there is one row added at the row of the "Active Cell".

Problem: Whenever I insert a row, instead of the macro inserting a
blank row containing only the formula from the row above, it will copy
the data along with the formula...which is not what I'm looking
for....

Here is the snippet of code that goes with my InsertRow macro:
Sub InsertCopiedRow()
ActiveSheet.Unprotect "****"
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveSheet.Protect "****"

End Sub

I have scoured newsgroups and MSVP Excel pages....and it seems like
everyone is pointing at doing some sort of changes with the formulas
AND the actual macro.....especially with some sort of OFFSET function.
I am open to making this work, but I cannot figure out how to insert
OFFSET properly into my formulas/macros if necessary....

Please see to get a visual of the worksheet:
http://php.indiana.edu/~cawagner/macro.jpg

Any help would be greatly appreciated......

Roger

This is not a "fixed size" template at all. Actually, our reasoning
for assigning the Insert Row macro to the Add Lines button is b/c we
will need to make use of 2-3 page printouts....

Thanks

"Bill Renaud" wrote in message ...
This appears to be a template that is fixed as far as vertical size is
concerned. In other words, do you really want to insert a new row and
possibly cause the printout to wrap onto a second page, or do you simply
want to have the formulas show values when you start entering data on a new
row?

In this situation, I usually enter formulas in ALL of the cells (rows 8 thru
15 in your JPG screenshot), even though most of them will not have data. Set
up the formulas so that any values are 0 (or some other sentinel value) if
column $A or $B is blank. So, the formula in cell $L$10 would be:
=IF(ISBLANK(A10),0,0.375*K10)

Then format the values so that they do not show when the value is 0. See
the "Create a custom number format" topic in Excel Help. The format for this
example would be something like the following (notice the 2 semi-colons next
to each other near the end of the line for the 0 format):
#,##0.00;[Red] #,##0.00;;@

This completely eliminates the need to write any macros to add or delete a
line, which can get very messy when you have a "fixed size" form like this
to populate.
--
Regards,
Bill


"iltf124 no spam @hotmail.com" wrote in message
m...
Hello,
I know that something relative to this has been posted before but I
need to get a little more specific in this newsgroup in hopes that I
can get some help. Please keep in mind that I am not a developer, nor
an expert with Excel.

I have (somewhat unsuccessfully) written an excel macro to Insert a
black row while copying the formula and formatting from the row above
it. The macro is assigned to a button that....when the button is hit
....there is one row added at the row of the "Active Cell".

Problem: Whenever I insert a row, instead of the macro inserting a
blank row containing only the formula from the row above, it will copy
the data along with the formula...which is not what I'm looking
for....

Here is the snippet of code that goes with my InsertRow macro:
Sub InsertCopiedRow()
ActiveSheet.Unprotect "****"
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveSheet.Protect "****"

End Sub

I have scoured newsgroups and MSVP Excel pages....and it seems like
everyone is pointing at doing some sort of changes with the formulas
AND the actual macro.....especially with some sort of OFFSET function.
I am open to making this work, but I cannot figure out how to insert
OFFSET properly into my formulas/macros if necessary....

Please see to get a visual of the worksheet:
http://php.indiana.edu/~cawagner/macro.jpg

Any help would be greatly appreciated......

Roger

Try the following code. I think Excel copies everything from the row above
when it inserts a new row, so you might be able to eliminate some of the
code. I added some references to your example. They make debugging and
development so much easier. Single-step through the code and watch the
locals window to actually see the variables being set to the proper values
(rngCell, rngNewRow, etc.).

Sub InsertCopiedRow()
Dim ws As Worksheet
Dim rngCell As Range
Dim rngNewRow As Range
Dim lngNumColumns As Long

ActiveSheet.Unprotect "****"

Set ws = ActiveSheet
lngNumColumns = ws.UsedRange.Columns.Count

'Move down 1 row before inserting a row.
Set rngCell = ActiveCell.Offset(1, 0)
'Insert occurs above the range specified.
rngCell.EntireRow.Insert 'Shift:=xlDown

'Set rngNewRow to just the active cells, not the entire row.
'Note that rngCell is now down a row after the insert.
With ws
Set rngNewRow = .Range(.Cells(rngCell.Row - 1, 1), .Cells(rngCell.Row -
1, lngNumColumns))
End With

'Copy formulas and formats only.
'Note that this will re-define rngCell.
For Each rngCell In rngNewRow
With rngCell
'First copy everything (formats, formulas, etc.)
.Offset(-1, 0).Copy
.PasteSpecial xlPasteAll
Application.CutCopyMode = False

'Now delete any value copied from the row above.
If Not .HasFormula _
Then
.ClearContents 'Only clears the formula (and value).
.ClearComments
End If
End With
Next rngCell

ActiveSheet.Protect "****"
End Sub

--
Regards,
Bill

"iltf124 no spam @hotmail.com" wrote in message
om...
This is not a "fixed size" template at all. Actually, our reasoning
for assigning the Insert Row macro to the Add Lines button is b/c we
will need to make use of 2-3 page printouts....

Thanks

My only task left is to test whether or not I can get the formatting
of the cells to copy over along with the formulas. As you can tell by
looking at my image....http://php.indiana.edu/~cawagner/templatescreen2.jpg
the formulas copied over nicely...but I am wondering if there is
anything I can modify/add that will copy over the the merged cell as a
merged cell (instead of 3 separate ones). I am specifically talking
about columns B-D. Originally, I had B12 as a merged cell that merged
across columns B-D, but when I insert the row....it reverts back to 3
separate cells (similar to screen shot = b12, c12, d12 instead of just
B12).

Additionally, I have a drawing line going diagonal from K11:J11, is
there any code that will keep that intact when I insert the row?

"Bill Renaud" wrote in message ...
Try the following code. I think Excel copies everything from the row above
when it inserts a new row, so you might be able to eliminate some of the
code. I added some references to your example. They make debugging and
development so much easier. Single-step through the code and watch the
locals window to actually see the variables being set to the proper values
(rngCell, rngNewRow, etc.).

Sub InsertCopiedRow()
Dim ws As Worksheet
Dim rngCell As Range
Dim rngNewRow As Range
Dim lngNumColumns As Long

ActiveSheet.Unprotect "****"

Set ws = ActiveSheet
lngNumColumns = ws.UsedRange.Columns.Count

'Move down 1 row before inserting a row.
Set rngCell = ActiveCell.Offset(1, 0)
'Insert occurs above the range specified.
rngCell.EntireRow.Insert 'Shift:=xlDown

'Set rngNewRow to just the active cells, not the entire row.
'Note that rngCell is now down a row after the insert.
With ws
Set rngNewRow = .Range(.Cells(rngCell.Row - 1, 1), .Cells(rngCell.Row -
1, lngNumColumns))
End With

'Copy formulas and formats only.
'Note that this will re-define rngCell.
For Each rngCell In rngNewRow
With rngCell
'First copy everything (formats, formulas, etc.)
.Offset(-1, 0).Copy
.PasteSpecial xlPasteAll
Application.CutCopyMode = False

'Now delete any value copied from the row above.
If Not .HasFormula _
Then
.ClearContents 'Only clears the formula (and value).
.ClearComments
End If
End With
Next rngCell

ActiveSheet.Protect "****"
End Sub

--
Regards,
Bill

"iltf124 no spam @hotmail.com" wrote in message
om...
This is not a "fixed size" template at all. Actually, our reasoning
for assigning the Insert Row macro to the Add Lines button is b/c we
will need to make use of 2-3 page printouts....

Thanks

I re-wrote the routine as shown below, since the merged cell would get
complicated otherwise. Also, I did not include code to add the diagonal
drawing line the the Mileage cells in columns $K:$L, as I think this would
get VERY complex. Could you just leave columns $K and $L as normal (i.e.
label column $K "Mileage" and column $L "Mileage Cost")?

Also as another suggestion, I would insert a blank row immediately above
your Totals at the bottom and include that row in your formulas, but then
hide that row. This will prevent problems if the user is already on the last
data entry row (row 137?) and then they insert a new row. If this happened,
then you would have to include code in your macro to revise the formulas for
the Totals. There might still be a small chance that the last row would not
get summed up in the Totals if the user defeats your template somehow.

Sub InsertCopiedRow()
Dim ws As Worksheet
Dim rngCurrentRow As Range
Dim rngNewRow As Range

Application.ScreenUpdating = False
ActiveSheet.Unprotect "****"

Set ws = ActiveSheet
Set rngCurrentRow = ActiveCell.EntireRow

'Step down a row and insert a new row.
rngCurrentRow.Offset(1, 0).Insert Shift:=xlDown

'Now define the new row to be the newly inserted row.
Set rngNewRow = rngCurrentRow.Offset(1, 0)

'Copy the current row down to the new row.
'This will also copy the merged cells as is.
rngCurrentRow.Copy
ws.Paste Destination:=rngNewRow
Application.CutCopyMode = False

'New select and clear any constants and comments.
'Formulas and formats will remain.
With rngNewRow.SpecialCells(Type:=xlCellTypeConstants)
.ClearContents
.ClearComments
End With

'Select column $A for user convenience.
rngNewRow.Cells(1, 1).Select
ActiveSheet.Protect "****"
Application.ScreenUpdating = True
End Sub

--
Regards,
Bill


"iltf124 no spam @hotmail.com" wrote in message
om...
My only task left is to test whether or not I can get the formatting
of the cells to copy over along with the formulas. As you can tell by
looking at my
image....http://php.indiana.edu/~cawagner/templatescreen2.jpg
the formulas copied over nicely...but I am wondering if there is
anything I can modify/add that will copy over the the merged cell as a
merged cell (instead of 3 separate ones). I am specifically talking
about columns B-D. Originally, I had B12 as a merged cell that merged
across columns B-D, but when I insert the row....it reverts back to 3
separate cells (similar to screen shot = b12, c12, d12 instead of just
B12).

Additionally, I have a drawing line going diagonal from K11:J11, is
there any code that will keep that intact when I insert the row?

Add the following line immediately above the "With
rngNewRow.SpecialCells(Type:=xlCellTypeConstants)" line to prevent an error,
if the current row has no data in it:

On Error Resume Next
--
Regards,
Bill


"Bill Renaud" wrote in message
...
I re-wrote the routine as shown below, since the merged cell would get
complicated otherwise. Also, I did not include code to add the diagonal
drawing line the the Mileage cells in columns $K:$L, as I think this would
get VERY complex. Could you just leave columns $K and $L as normal (i.e.
label column $K "Mileage" and column $L "Mileage Cost")?

Also as another suggestion, I would insert a blank row immediately above
your Totals at the bottom and include that row in your formulas, but then
hide that row. This will prevent problems if the user is already on the
last
data entry row (row 137?) and then they insert a new row. If this
happened,
then you would have to include code in your macro to revise the formulas
for
the Totals. There might still be a small chance that the last row would
not
get summed up in the Totals if the user defeats your template somehow.

Sub InsertCopiedRow()
Dim ws As Worksheet
Dim rngCurrentRow As Range
Dim rngNewRow As Range

Application.ScreenUpdating = False
ActiveSheet.Unprotect "****"

Set ws = ActiveSheet
Set rngCurrentRow = ActiveCell.EntireRow

'Step down a row and insert a new row.
rngCurrentRow.Offset(1, 0).Insert Shift:=xlDown

'Now define the new row to be the newly inserted row.
Set rngNewRow = rngCurrentRow.Offset(1, 0)

'Copy the current row down to the new row.
'This will also copy the merged cells as is.
rngCurrentRow.Copy
ws.Paste Destination:=rngNewRow
Application.CutCopyMode = False

'New select and clear any constants and comments.
'Formulas and formats will remain.
With rngNewRow.SpecialCells(Type:=xlCellTypeConstants)
.ClearContents
.ClearComments
End With

'Select column $A for user convenience.
rngNewRow.Cells(1, 1).Select
ActiveSheet.Protect "****"
Application.ScreenUpdating = True
End Sub

--
Regards,
Bill


"iltf124 no spam @hotmail.com" wrote in message
om...
My only task left is to test whether or not I can get the formatting
of the cells to copy over along with the formulas. As you can tell by
looking at my
image....http://php.indiana.edu/~cawagner/templatescreen2.jpg
the formulas copied over nicely...but I am wondering if there is
anything I can modify/add that will copy over the the merged cell as a
merged cell (instead of 3 separate ones). I am specifically talking
about columns B-D. Originally, I had B12 as a merged cell that merged
across columns B-D, but when I insert the row....it reverts back to 3
separate cells (similar to screen shot = b12, c12, d12 instead of just
B12).

Additionally, I have a drawing line going diagonal from K11:J11, is
there any code that will keep that intact when I insert the row?