Thread: Autofill formulas and formats to last data row only

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.

Cellpack #        Initial mass        Final Mass      Total Inside      Weight    .......etc.
1                     x(i) 1                 x(f)1              =x(f)-x(i) 1         =xxx1
2                     x(i) 2                 x(f)2              =x(f)-x(i) 2         =xxx2
.....

Sub Builddatasheet2()


Dim cellpackcount As Integer
Dim i As Integer

ActiveSheet.Unprotect ("QA")
cellpackcount = Range("c5").Value
For i = 1 To cellpackcount - 1

    Range("A24:A33").Select
    Selection.EntireRow.Insert
    Next
    
    Range("A21:H21").Select
    Selection.AutoFill Destination:=Range("A21:H21"), Type:=xlFillSeries
    Range("A21:H21").Select
    Range("C14").Select
    ActiveSheet.Protect ("QA")
    
End Sub

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.

 
   1 Dim cellpackcount As Integer
   2 Dim i As Integer
    
   3 cellpackcount = Range("c5").Value   'get the number of cellpacks
   4 For i = 1 To cellpackcount - 1      '# of cellpacks to be added is 1 less than the total
   5    Range("A21:A30").Select         'select 10 rows (enough for 1 cellpack)
   6    Selection.EntireRow.Insert      'insert enough rows for 1 cellpack
   7    Next                                'repeat for the next cellpack, if any
   8  Range("FormulaRow").Select          'get the formulas that need to be copied to the new rows
   9  Selection.AutoFill Destination:=Range("B20:lastdatarow"), Type:=xlFillDefault   'copy the formulas to the new rows
  10  Range("c13").Select                 'put the cursor on the first data row

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?

Sub BuildDataSheet3()

' BuildDataSheet Macro
' Macro recorded 2/15/99 by Cindy L. Ellis

    Dim cellpackcount As Integer
    Dim I As Integer
    
    ActiveSheet.Unprotect ("QA")
    cellpackcount = Range("c5").Value   'get the number of cellpacks
    For I = 1 To cellpackcount - 1      '# of cellpacks to be added is 1 less than the total
        Range("A26:A35").Select         'select 10 rows (enough for 1 cellpack)
        Selection.EntireRow.Insert      'insert enough rows for 1 cellpack
      Next                                         'repeat for the next cellpack, if any
    Range("FormulaRow").Select        
   Range("B25:F25").AutoFill Destination:=Range("B25:F" & Cells(Rows.Count, "B").End(xlUp).Row)
   Range("c16").Select
   
   
   
    
    ActiveSheet.Protect ("QA")

End Sub