Help with Report

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

Board Regular
Joined
Aug 22, 2005
Messages
215
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

Board Regular
Joined
Jun 1, 2003
Messages
130
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,993
Messages
5,545,377
Members
410,679
Latest member
rolandbianco
Top