Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.
Course Of The Month
9 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Add a new row to the "database" (macro), just copy copy formulas and formats (excel 2003)
Posted on 2011-03-08
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
3
9 Comments
JP, Something like this, with the name StartData added to cell C7?
Thomas
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
Thomas
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
Active 1 day ago
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
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
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.