DataMgmtAnalyst7
New Member
- Joined
- Dec 2, 2020
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
I am running a code that copies data for a specific criteria and moves it from one worksheet to a new worksheet (Sheet2). However, I am having an issue where if I run the code a second time for new values then it deletes all the previous data from Sheet2 and copies the new data over. So, I am trying to figure out how I can edit the code so that when it paste the values to Sheet2 it always pastes them in the next available row instead of overwriting the existing data?
VBA Code:
Sub MoveCompleted()
'
'MoveCompleted Macro
'
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("$A$1:$AW$" & lastRow).AutoFilter Field:=27, Criteria1:="Yes"
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("EFT").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Range("B8165").Select
ActiveSheet.ShowAllData
Range("B2").Select
End Sub