Every time I need add something to the sheet I need copy a row with all the formulas and formatting and change it contents, is there any way each new row has a pre defined template with formatting and formulas?
Every time I need add something to the sheet I need copy a row with all the formulas and formatting and change it contents, is there any way each new row has a pre defined template with formatting and formulas?
Last edited by Henrique; 09-23-2009 at 10:45 AM.
Hi, in Excel 2003 you can define your data table as a List. Highlight the table, including the column labels and then click Data - List - Create List and then hit OK.
Now you should see a blue border around the data table. When you click inside the table, there will be a new empty row added to the bottom of the list, where formatting and calculations are automatically applied as in the cells above.
Hum, not exactly what I asked. Attached is a sample sheet.
You can see every two row correspond to the item on the B column. b3,b5,b7 ... I would like to create a template where every time I want add a new item I dont need to copy the cells, paste and replace the content.
Would be nice to have the template on the top of the sheet, every time I fill the blank spaces it adds the group of two new rows containing all the data and formula to the end of the sheet and the template space get empty again to enter new values ...
Starting from your screenshot:
place the cursor in cell A23
start the macro recorder
set it to relative cell referencing
copy the two rows above the active cell
do your paste and tidy up thing
stop the macro recorder
Next time you want to insert a new set of rows, place the cursor in Column A in the next empty row and run the macro.
Lots of fine tuning can be done here, but this should give you a starting point. You could create a button somewhere on the sheet that runs the macro, you could use code to find the last empty cell by itself ....
try this in the worksheet
i think its an old "Jack in the UK" codePlease Login or Register to view this content.
I did what teylyn said and worked fine, thanks.
I have not tried what Pike said since Im very new to excel and VB, I couldnt figure what variables to change in the code to get it working.
The range is Range("A1:X2")
Thanks,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks