Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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
0
easycapital
Asked:
easycapital
  • 5
  • 3
1 Solution
 
nutschCommented:
JP, Something like this, with the name StartData added to cell C7?

Sub asdgfasg()

With Range("startData")

    .Offset(1).EntireRow.Insert
    .Offset(2).EntireRow.Copy .Offset(1).EntireRow
    .Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

End With

End Sub

Open in new window


Thomas
0
 
nutschCommented:
Here is is in a file

Thomas
Macro--Add-rows---same-format-an.xls
0
 
easycapitalAuthor Commented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
nutschCommented:
Glad to help again, thanks for the grade.

Thomas
0
 
byundtCommented:
If the first row contains all formulas, you will get a fatal error with Thomas' macro. Here is the patch:
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

Open in new window


Brad
Macro--Add-rows---same-formatQ26.xls
0
 
nutschCommented:
touché

T
0
 
easycapitalAuthor Commented:
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
0
 
nutschCommented:
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

Open in new window




0
 
easycapitalAuthor Commented:
Brad and Thomas,

Resolved, thank you very much.  

Real time saver.

JP

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now