I made a macro button that will copy rows based on bat species and paste them into a different sheet for each species. The button works as expected, but if I add new data to the main sheet (example data shown below), the next time I click the button it will add all the data for each species to their corresponding sheets, including old data that has already been entered. Is there a way to edit the code to overwrite cells that have a matching file name to avoid duplicates, or something to that effect? code shown below sample data. Thanks!
File- Date- Time- Date and Time- Species- Species Code
CRATR_20210220_185831- 2/20/2021- 18:56:31- 2/20/21 18:56:31- Big Brown Bat- 2
CRATR_20210220_203858- 2/20/2021- 20:38:58- 2/20/21 20:38:58- Big Brown Bat- 2
CRATR_20210220_204011- 2/20/2021- 20:40:11- 2/20/21 20:40:11- Big Brown Bat- 2
CRATR_20210221_201502- 2/21/2021- 20:15:02- 2/21/21 20:15:02- Silver Haired Bat- 1
CRATR_20210221_202415- 2/21/2021- 20:24:15- 2/21/21 20:24:15- Silver Haired Bat- 1
CRATR_20210226_213920- 2/26/2021- 21:39:20 2/26/21 21:39:20- Silver Haired Bat- 1
CRATR_20210301_200556- 3/1/2021- 20:05:56- 3/01/21 20:05:56- Silver Haired Bat- 1
CRATR_20210301_214441- 3/1/2021- 21:44:41- 3/01/21 21:44:41- Western Small Footed Bat- 3
CRATR_20210301_221213- 3/1/2021- 22:12:13- 3/01/21 22:12:13- Western Small Footed Bat- 3
Private Sub CommandButton1_Click()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sheet1").Cells(i, 5).Value = "Silver Haired Bat" Then
Worksheets("Sheet1").Rows(i).Copy
Worksheets("Sheet3").Activate
b = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet3").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select
End Sub
Bookmarks