Fill Down VBA Script

[ZappAstrim]

Hello all - I've been chasing through the internet for the last hour and cannot figure this one out. Perhaps you guys can give me a hand.

I have a spreadsheet and have found the perfect script for inserting the number of rows I require as well as copying the formulas I need. This really is a beauty:

Sub InsertRow()
    Dim Rng, n As Long, k As Long
    Application.ScreenUpdating = False
    Rng = InputBox("Enter number of rows required.")
    If Rng = "" Then Exit Sub
    Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
    'need to know how many formulas to copy down.
    'Assumesfrom A over to last entry in row.
    k = ActiveCell.Offset(-1, 0).Row
    n = Cells(k, 256).End(xlToLeft).Column
    Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
End Sub

Now the thing is that I have a column in my sheets which is always contained in column "B" but the row where the data ID begins is variable. I need to have a unique ID for each data point. So in the example here it's C0001, C0002, C0003 etc. When using this VBA script I loose the continuous numbering and might end up with 200 C0010 data points. Can you help me out with making this work (and making it variable too)?

Please find the example workbook here:
http://dl.dropbox.com/u/15735073/test.xlsm

 

[Sektor]

In other words, you need to insert row(s) and auto-fill them?

 

[ZappAstrim]

In other words, you need to insert row(s) and auto-fill them?

Yes, but I am already doing that. I also need the script to make sure that the values in column b are numbered sequentially. If you download the test spreadsheet everything will become clear.

 

[Sektor]

Use "Type:=xlFillDefault" argument for AutoFill.

 

[ZappAstrim]

Use "Type:=xlFillDefault" argument for AutoFill.

Sorry, I'm a total n00b to VBA. Could you please give me a little more detail on where I need to put this.

 

[Sektor]

Put at the end.

Cells(ActiveCell.Row, "B").Resize(Val(Rng)).FillDown Type:=xlFillDefault

 

[ZappAstrim]

Put at the end.

Cells(ActiveCell.Row, "B").Resize(Val(Rng)).FillDown Type:=xlFillDefault

Now I get a run-time error '1004': Application-defined or object defined error.

 

[ZappAstrim]

Does anyone else have a good idea for this?

 

[ZappAstrim]

After much hard work I managed to implement the programme just as I need it:

Sub InsertRow()

    With ThisWorkbook
        Application.ScreenUpdating = False
        Dim cell1, cell2 As Range
        Dim rng, n As Long, k As Long
        rng = InputBox("Enter number of rows required.")
        If rng = "" Then Exit Sub
        Set cell1 = Cells.Find(What:="Grand Total").Offset(-1, 0)
        Set cell2 = cell1.Offset(rng - 1, 0)
        Range(cell1, cell2).EntireRow.Insert
        'Set cell1 = Cells.Find(What:="Total").Offset(-1, 0)
        'cell1.EntireRow.Clear
        'cell1.Offset(-rng - 1, 0).EntireRow.Select
        Range(cell1.Offset(-rng - 1, 0), cell1.Offset(-rng - 1, 200)).Select
        'cell2.Offset(-rng + 1, 0).EntireRow.Select
        Selection.AutoFill Destination:=Range(cell1.Offset(-rng - 1, 0), cell2.Offset(-rng + 1, 200)), Type:=xlFillDefault
   End With
   
End Sub