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:
Any help would be appriciated.
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.