Help with Report

[help_questions]

I have an excel sheet with a list of data in rows 3-10, and row 11 being the totals row. Everytime I insert a row, I have to adjust my totals formulas to account for the inserted row. Is there a way totals to avoid adjusting my formulas?
and..
How do I write a macro to insert a row above the TOTALS row, and then copy and paste into the inserted row? I should be able to figure out the copy and paste part, but have no idea on how to find the totals row and insert a row above the TOTALS row....

any help would be appreciated.

 

[larrydunn]

The easiest way is to put your totals on row 12, including the blank row 11 just above it in the SUM. Then adjust the row height of row 11 to 1 or 2, so you don't type anything into it. You can put a border on these cells, if you want.

Then you can insert rows to your heart's content. And you've managed to avoid an unnecessary macro!

 

[help_questions]

I tested the blank row tip..this works great.

What I need now is a marco that will add a row above this blank row and copy data into the new row.

Or a macro that will find the greatest date in column A and then insert a row below the greatest date and insert values into this new row...

 

[larrydunn]

Here's a macro that searches for the "blank" row on the assumption that it is set to a row height of 2. It then captures the data from a row above and copies it into the new row, then returns to the original selection.

Note that you will have to adjust the items in the Assumptions section.

Sub OpenNewRow()
'Go to 'blank' row and insert new row
Dim sCurrentSelection As String
Dim sngRowHeight As Single
Dim sLeftColumn As String
Dim sRightColumn As String

'Assumptions:
'Blank row has height of 2
sngRowHeight = 2
'Left column is at A
sLeftColumn = "A"
'Right column is at J
sRightColumn = "J"

'Remember where we were at start
sCurrentSelection = Selection.Address

FindBlankRow:
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
If ActiveCell.RowHeight > sngRowHeight Then GoTo FindBlankRow

Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").Select 'Move up
ActiveSheet.Range(sLeftColumn & ActiveCell.Row & ":" & sRightColumn & ActiveCell.Row).Select
Selection.Copy
ActiveSheet.Range(sLeftColumn & ActiveCell.Row + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Return to original selection
ActiveSheet.Range(sCurrentSelection).Select
End Sub