VBA lookup and delte

[david7281]

Hi I have read about the vlookup fomulas but I need it slightly diffrent.

I need it to look up a member number on the active sheet (sheet database) in cell b3 then it needs to go to my other sheets called database and it should go and delete that row.

This is my code but I need this code at the bottom
Sub Archive()
'
' Archive Macro
'

'
Range("B2").Select
Selection.Copy
Sheets("Archive").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A26").Select
Sheets("Database").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Archive").Select
Range("b" & Cells(Rows.Count, "b").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Database").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Archive").Select
Range("c" & Cells(Rows.Count, "c").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Database").Select
Range("A15").Select
Application.CutCopyMode = False

End Sub



Many Thanks
David

[Bob Phillips]

Sub Archive()
Dim lastrow As Long

With Sheets("Archive")

lastrow = .Cells(.rows.Count, "A").End(xlUp).Row
Worksheets("Database").Range("B2").Copy .Range("A" & lastrow + 1)

lastrow = .Cells(.rows.Count, "B").End(xlUp).Row
Worksheets("Database").Range("B6").Copy .Range("B" & lastrow)

lastrow = .Cells(.rows.Count, "C").End(xlUp).Row
Worksheets("Database").Range("B13").Copy .Range("c" & lastrow)
End With

Workheets("Database").rows(3).Delete
End Sub