Solved

Add a new row to the "database" (macro), just copy copy formulas and formats (excel 2003)

Posted on 2011-03-08
9
271 Views
Last Modified: 2012-05-11
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
Comment
Question by:easycapital
  • 5
  • 3
9 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35075725
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
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35075730
Here is is in a file

Thomas
Macro--Add-rows---same-format-an.xls
0
 

Author Closing Comment

by:easycapital
ID: 35075792
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35075801
Glad to help again, thanks for the grade.

Thomas
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:byundt
ID: 35075827
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35075838
touché

T
0
 

Author Comment

by:easycapital
ID: 35075932
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35075997
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
 

Author Comment

by:easycapital
ID: 35076034
Brad and Thomas,

Resolved, thank you very much.  

Real time saver.

JP

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now