hi
can some one pl help on this?
here is the code for getting the data from 1 sheet and copies the same to anther sheet after copies i want to delete the same row from
1 sheet and move the remaining data row up in the 1 sheet
any help will be appreciated
thanks
Private Sub Enterdata_Click()
Dim n As Long
Dim ws As Worksheet
Dim R As Range
Dim var
Dim i&
With Worksheets("Brought")
n = Application.Match((Me.CombTicker.Value), .Range("A:A"), 0)
.Cells(n, 4).Value = Me.noofstock.Text
End With
If Trim(Me.CombTicker.Value) = "" Then
Me.CombTicker.SetFocus
MsgBox "Please enter a Ticker"
Exit Sub
End If
Set ws = Worksheets("Historical Data")
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If Me.CombTicker.Value = ws.Cells(i, 1).Value Then
If MsgBox("Ticker already exists in records. Do you still want to add the data to the records?", vbYesNo) = vbNo Then
Exit Sub
Else
Exit For
End If
End If
Next i
Set Rng = ws.Range("A2")
Set RngEnd = ws.Cells(Rows.Count, Rng.Column).End(xlUp)
NextRow = IIf(RngEnd.Row < Rng.Row, Rng.Row, RngEnd.Row + 1)
With Worksheets("Brought")
If Trim(.Cells(n, "A")) = Trim(CombTicker.Value) And _
Trim(.Cells(n, "D")) = Trim(noofstock.Value) Then
With ws
.Cells(NextRow, "A").Resize(1, 10) = Worksheets("Brought").Cells(n, "A").Resize(1, 10).Value
.Cells(NextRow, "K").Value = Me.DTPicker1.Value
.Cells(NextRow, "L").Value = Me.txtHighofDay.Value
.Cells(NextRow, "M").Value = Me.txtLowofDay.Value
.Cells(NextRow, "N").Value = Me.txtExitPrice.Value
.Cells(NextRow, "O").Value = Me.txtcommission.Value
End With
End If
If MsgBox("Delete Data row from Brought Sheet and move the row up?", vbYesNo) = vbYes Then
Cancel = True
Dim c As Range
With Sheets("Brought").Range("Ticker")
Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then drow = c.Row
c.EntireRow.Delete
End With
End If
End With
'clear the data
Me.CombTicker.Value = ""
'Me.txtExitDate.Value = Format(Date, "Medium Date")
Me.txtExitPrice.Value = ""
Me.txtcommission = 7
Me.txtHighofDay.Value = ""
Me.txtLowofDay.Value = ""
Me.CombTicker.SetFocus
End Sub
Bookmarks