asked on Add a new row to the "database" (macro), just copy copy formulas and formats (excel 2003)
Using the left most cell of the header (in bold), perhaps creating assigning a name to it. So the the length of the first row below can be identified, then:
-Copy the first row below the header (this 2007 verbiage, my apologies, but we doing this in 2003)
-Paste (only formats and formulas) and move down the current cell contents down one row.
This simplifies the adding of new data to the "table". Please include excel workbook with the solution.
See attached.
Thanks,
Juan![sss]()
Macro--Add-rows---same-format-an.xls
-Copy the first row below the header (this 2007 verbiage, my apologies, but we doing this in 2003)
-Paste (only formats and formulas) and move down the current cell contents down one row.
This simplifies the adding of new data to the "table". Please include excel workbook with the solution.
See attached.
Thanks,
Juan
Macro--Add-rows---same-format-an.xls
Microsoft Excel
Last Comment
easycapital
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thomas,
Real nice. It looks good. Just tried it, I guess time is the best test.
Will post again if I encounter problems.
Thanks!
JP
Real nice. It looks good. Just tried it, I guess time is the best test.
Will post again if I encounter problems.
Thanks!
JP
Glad to help again, thanks for the grade.
Thomas
Thomas
If the first row contains all formulas, you will get a fatal error with Thomas' macro. Here is the patch:
Brad
Macro--Add-rows---same-formatQ26.xls
Sub NewRecord()
Dim rg As Range
Application.ScreenUpdating = False
With Range("HeaderRow")
.Offset(1).EntireRow.Insert
.Offset(2).Copy .Offset(1)
On Error Resume Next
Set rg = .Offset(1).SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rg Is Nothing Then rg.ClearContents
End With
Application.ScreenUpdating = True
End Sub
Brad
Macro--Add-rows---same-formatQ26.xls
touché
T
T
ASKER
Brad,
Great, and thanks for sharing that one! I am wondering why does the new row inherit the height of the header row and not of the row below the header. Ie. try increasing the height of the header row and then apply macro.
JP
Great, and thanks for sharing that one! I am wondering why does the new row inherit the height of the header row and not of the row below the header. Ie. try increasing the height of the header row and then apply macro.
JP
inserting a row gets the default format from the row above, that's why. So an update to avoid the row height issue could be:
Sub asdgfasg()
With Range("startData")
.Offset(2).EntireRow.Insert
.Offset(1).EntireRow.Copy .Offset(2).EntireRow
On Error Resume Next
.Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
End Sub
ASKER
Brad and Thomas,
Resolved, thank you very much.
Real time saver.
JP
Resolved, thank you very much.
Real time saver.
JP
Open in new window
Thomas