VBA Code insert new line & Formulas

Alteran

Board Regular
Joined
Nov 25, 2009
Messages
117
I am trying to make this code insert a new line from a user sheet, and add some formulas for me, however for some reason it keeps adding a line not on the LastRow as I specify It over wrights the whole first row. I am not sure where I am going wrong, and could use an extra set of eyes to look to see if they see anything:

Code:
Private Sub cbPost_Click()
Dim LastRow As Long
'This Code addes the line
    Rows("6:6").Select
    Static answer
    answer = 1
            
    If answer > 0 Then
      Dim Counter
      Counter = 0   ' Initialize variables.
    
      Do While Counter < answer
        Counter = Counter + 1
        Sheets("Contracts").Select ' Selects the contracs sheet
        Range("Last_Cont").EntireRow.Insert Shift:=xlDown ' Goes to the line names Last_cont and insterts a new row
      Loop
    End If
        
    'Goes to the last row with anything in it in the Worksheet in column A
    LastRow = Worksheets("Contracts").Range("A65536").End(xlUp).Row + 1
    'Puts values in cells (looks at the off set # meaning columns count)
        Cells(LastRow, 2).Value = tbItem
        'Puts the Contract Item row # in
        Range("A6").Formula = "=A5+1"
        Range("A6").Copy Destination:=Range("A6:A" & (LastRow - 1))
        Cells(LastRow, 3).Value = tbContNum
        Cells(LastRow, 4).Value = tbContName
        Cells(LastRow, 5).Value = tbLead
        
        'Checking to see which FY is selected as the first year if none give a blank
            If obtn1 = True Then
                Cells(LastRow, 7).Value = "2009/2010"
            ElseIf obtn2 = True Then
                Cells(LastRow, 7).Value = "2010/2011"
            ElseIf obtn3 = True Then
                Cells(LastRow, 7).Value = "2011/2012"
            Else
                Cells(LastRow, 7).Value = ""
            End If
            
        'Checking to see which FY is selected as the Second year if none give a blank
        If tbFY1Money = "" Then
        Cells(LastRow, 11).Value = (0 * 1)
        Else
        Cells(LastRow, 10).Value = (tbFY1Money) * 1
        End If
            If obtn4 = True Then
                Cells(LastRow, 9).Value = "2009/2010"
            ElseIf obtn5 = True Then
                Cells(LastRow, 9).Value = "2010/2011"
            ElseIf obtn6 = True Then
                Cells(LastRow, 9).Value = "2011/2012"
            Else
                Cells(LastRow, 9).Value = ""
            End If
        If tbFY2Money = "" Then
        Cells(LastRow, 11).Value = (0 * 1)
        Else
        Cells(LastRow, 10).Value = (tbFY2Money) * 1
        End If
        Cells(LastRow, 11).Value = tbStart
        Cells(LastRow, 12).Value = tbEnd
        'Puts the formula in Column M
        Range("M6").Formula = "=IF(($H6+$J6)>50000,""Yes"","""")"
        Range("M6").Copy Destination:=Range("M6:M" & LastRow)
        Cells(LastRow, 16).Value = tbOfficer
        Cells(LastRow, 17).Value = tbDeliverable
        Cells(LastRow, 18).Value = tbAward
        Cells(LastRow, 19).Value = tbNotes
        'Puts the formula in Columns U, V & W
        Range("U6").Formula = "=((IF($G6=""2009/2010"",$H6,0))+(IF($I6=""2009/2010"",$J6,0)))"
        Range("U6").Copy Destination:=Range("U6:U" & LastRow)
        Range("V6").Formula = "=((IF($G6=""2010/2011"",$H6,0))+(IF($I6=""2010/2011"",$J6,0)))"
        Range("V6").Copy Destination:=Range("V6:V" & LastRow)
        Range("W6").Formula = "=((IF($G6=""2011/2012"",$H6,0))+(IF($I6=""2011/2012"",$J6,0)))"
        Range("W6").Copy Destination:=Range("W6:W" & LastRow)
    Columns("A:A").Select ' Selects column A
    Selection.Copy 'Copies Column A
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False ' These two lines past special values to column A only
    Application.CutCopyMode = False
End Sub

Any help would be appriciated.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top