PDA

View Full Version : VBA lookup and delte



david7281
02-12-2013, 02:32 AM
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

xld
02-12-2013, 04:37 AM
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