Macro for copying formulas + inserting rows

I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:

http://www.megaupload.com/?d=1W6PBADE

On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.

Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.

Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.

I hope I made sense

Thanks
Marco-Kun

Put your cursor anywhere in your existing list.

Press CTRL-L to activate Excel's built-in "LIST" feature.

Now use your buttons to add an entry to the bottom of the list...zz-something. When you click ADD ANIME it will put at the end, the LIST function will extend the existing "LIST" by one row and the cells with formulas in them will be copied down automatically.

When I do this, the buttons seems to move down on their own.

Hm, it does for me. How odd.

OK. Make sure you've turned on the LIST function. Then change the ADD ANIME code to this:

Option Explicit

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Dim ShTop As Long
Set ws = Worksheets("MyAnimeList")

'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.Title.Value) = "" Then
  Me.Title.SetFocus
  MsgBox "Please enter an Anime title"
  Exit Sub
End If

'copy the data to the database
With ws
  .Cells(lRow, 1).Value = Me.Title.Value
  .Cells(lRow, 2).Value = Me.TypeE.Value
  .Cells(lRow, 3).Value = Me.TotalEpisodes.Value
  .Cells(lRow, 4).Value = Me.DLEps.Value
  .Cells(lRow, 5).Value = Me.WatchedEpisodes.Value
End With
    ActiveSheet.Shapes("Lint omlaag 4").Select
    ShTop = Selection.Top + 12.75
    ActiveSheet.Shapes("Lint omlaag 4").Top = ShTop
    
    ActiveSheet.Shapes("Plus 5").Select
    ShTop = Selection.Top + 12.75
    ActiveSheet.Shapes("Plus 5").Top = ShTop
    

'clear the data
Me.Title.Value = ""
Me.TypeE.Value = ""
Me.TotalEpisodes.Value = ""
Me.DLEps.Value = ""
Me.WatchedEpisodes.Value = ""
Me.Title.SetFocus
Cells(lRow + 3, 1).Select
End Sub

I've colored my additions to make them easy to spot.

If your problem is resolved please mark your thread as Solved

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save