Autofill formulas and formats to last data row only

**hlhought** · 06-29-2010, 06:45 PM

So I have been trying to build this data sheet for work. I'd like to use a macro because our sample size keeps changing and we have to re-validate the spreadsheet everytime we change anything (which is a long process). Here is what I need: I need to be able to enter a value in "C5" and then hit the macro button and it will add 10 rows per integer (like if i put 3 in the box,I need 30 rows). I got that part- but I also need all of the formulas and stuff to fill into the following rows. So the current data table looks like something below and starts with 10 rows (cellpack #1-10). Then, I just want to be able to tell the macro to build the data sheet based on the value in "C5" and copy all formatting and formulas to the inserted rows. However, it's not working using the code below- it only copies the last 2 columns. I am really new at macros, but this will save me loads of time in the end. Any suggestions would be spectacular.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**pike** · 06-30-2010, 07:58 AM

Hi hlhought

try..

Please Login or Register  to view this content.

**hlhought** · 06-30-2010, 11:16 AM

Thanks for your help! It almost works, but still columns B,C, and D do not copy down for some reason. Only column E and F do. Also, the formulas don't fill in the inserted cells either.

Some more info on the spreadsheet: Column B simply assigns a number to the data in columns C-F (starting at 1 and increasing only by one per cell-1,2,3...). Columns C and D are columns where the user will input their data. Columns E and F will be calculated by excel using the data in columns C and D.

So I'm thinking that somehow column B needs to copy and fill series down- but I can't figure out how to do that- and I am super lost as to why columns C and D do absolutely nothing. Here is the original code that I am sort of revising to fit my spreadsheet. It works when I use the spreadsheet that it was built for (almost exactly the same as mine), but if I just try to apply the macro to my spreadsheet, it tries to debug at line 9. Since I know close to nothing about macros, this is only speculation, but I feel that "lastdatarow" should be defined somewhere but I cannot figure out where...this is the only macro code for the spreadsheet. The only difference in mine is that I need to shift the data down 3 rows.

Please Login or Register  to view this content.

**pike** · 06-30-2010, 06:36 PM

try

Please Login or Register  to view this content.

**hlhought** · 07-02-2010, 12:19 PM

Thanks again for your help!

Okay, so I tried that and it still left rows B,C, and D blank. However, I thought about what you had and then I wrote the code as follows. It's ALMOST there- but the problem is that this code is telling it to stop filling at the last data row in B. I don't exactly see why, but for some reason it is adding an extra 4 rows of data at the bottom. Other than that it is working...attached is a screen shot of the worksheet (I'm not sure that I'm allowed to upload the excel file.). Any more suggestions?

Please Login or Register  to view this content.

**pike** · 07-03-2010, 02:37 AM

Hi Cindy
What is the named range? can you attach the workbook then its easily solved.

**royUK** · 07-03-2010, 03:02 AM

Why do you need code to do this. Excel 2007 has enhanced Table features, you can even achieve this in Excel 2003 without code

You can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that it's much easier to work with. New or improved functionality for tables includes the following features.

Table header rows Table header rows can be turned on or off. When table headers are displayed, they stay visible with the data in the table columns by replacing the worksheet headers when you move around in a long table.

Calculated columns A calculated column uses a single formula that adjusts for each row. It automatically expands to include additional rows so that the formula is immediately extended to those rows. All that you have to do is enter a formula once—you don't need to use the Fill or Copy commands.

Automatic AutoFiltering AutoFilter is turned on by default in a table to enable powerful sorting and filtering of table data.

Structured references This type of reference allows you to use table column header names in formulas instead of cell references, such as A1 or R1C1.

Total rows In a total row, you can now use custom formulas and text entries.

Table styles You can apply a table style to quickly add designer-quality, professional formatting to tables. If an alternate-row style is enabled on a table, Excel will maintain the alternating style rule through actions that would have traditionally disrupted this layout, such as filtering, hiding rows, or manual rearranging of rows and columns.

**hlhought** · 07-06-2010, 06:10 PM

Hi Pike,

I really appreciate you taking the time to help me with this! Here is the spreadsheet.

I will need to do some more editing on the spreadsheet, so if there is something in particular that might help me in the future when editing this spreadsheet, please let me know

Thanks again!

RoyUK,

Thanks for the advice. I need to use a macro because the sheet will be locked after I finish it. I have to validate the spreadsheet and make sure everything works (it's a long process) and then when it is released to the employees, they can only press buttons and input data in specific cells. All formatting and other features will be turned off for them.