0

I want to Copy and Paste Rows from one excel tab to another whenever a criteria is met. Currently, this is my code which I learnt from an online tutorial. Is there a way I can modify the code below to prevent overwriting and copying the same data twice whenever I click the button?

Private Sub CommandButton1_Click()

a = Worksheets("Results").Cells(Rows.Count, 1).End(xlUp).Row

For i = 5 To a

    If Worksheets("Results").Cells(i, 29).Value = "Nutella" Then


        Worksheets("Results").Rows(i).Copy
        Worksheets("Nutella").Activate
        b = Worksheets("Nutella").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Nutella").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Results").Activate

    End If



Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Results").Cells(1, 1).Select

End Sub

Thanks

Vityata
  • 42,633
  • 8
  • 55
  • 100
wb0514
  • 1
  • 2
    Hi. You might want to get rid off Select and Activate stuff: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Then you might want to [Insert](https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-insert-method-excel) the copied range. – Vincent G Jun 26 '18 at 08:50
  • 1
    Is there any unique columns in both sheets that you can match to find the duplicates? – nagarajannd Jun 26 '18 at 08:50
  • 1
    Oh, it seems that by "overwriting" you didn't mean overwriting but duplicating? – Vincent G Jun 26 '18 at 08:52
  • Yea, I mean without duplicating information that was already inserted. Sorry I've only just started on VBA so my grasp on the commands are weak as well. – wb0514 Jun 26 '18 at 08:55
  • Could someone help me with code that I could put in? Thanks – wb0514 Jun 26 '18 at 09:22
  • 1
    @wbengj, be patient please. After all, people are doing this on their own free will – Samuel Hulla Jun 26 '18 at 09:24
  • @wbengj - give some screenshots and try to explain more precisely what is going wrong. I only see a loop that copies and pastes values, which does not help me understand the problem. – Vityata Jun 26 '18 at 09:37

0 Answers0