VBA Copy and Paste With Duplicates

[pahickham]

Hello All,

I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is glitchy, probably due to the multiple activate sheets portion. However, I need to add in the capability of clearing non-duplicate on the "2B Board Items" work sheet after every click of the command button. For instance if a cell from Column D of "All Data" is the same as a cell from column D for "2B Board Items" then don't delete it. Any help is appreciated.

Also any help on making the Macro more efficient would be appreciated

Private Sub CommandButton2_Click()

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

For i = 2 To a

If Worksheets("All Data").Cells(i, 8).Value > 0 Then

Worksheets("All Data").Rows(i).Copy
Worksheets("2B Board Items").Activate
b = Worksheets("2B Board Items").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("2B Board Items").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("All Data").Activate

End If

Application.CutCopyMode = False

Next

Worksheets("2B Board Items").Activate
ThisWorkbook.Worksheets("2B Board Items").Cells(1, 1).Select

End Sub

 

[JLGWhiz]

For clairification:
1. Do you want to 'Delete' or 'Clear Contents' ? Big difference. Delete requires shift of cells.
2. If Delete, do you want to delete cell in column D only, or entire row?
3. Do you want a seperate procedure or incorporate into current procedure?
4. Is Column D the only column affected?

 

[pahickham]

For clairification:
1. Do you want to 'Delete' or 'Clear Contents' ? Big difference. Delete requires shift of cells.
2. If Delete, do you want to delete cell in column D only, or entire row?
3. Do you want a seperate procedure or incorporate into current procedure?
4. Is Column D the only column affected?

I apologize for lack of clarity.
The Command button is on a 3rd sheet that will have inputs that can change weekly. Those manual inputs determine if the values in:
[If Worksheets("All Data").Cells(i, 8).Value > 0 Then]
will be greater than 0. So after those inputs are typed the command button will be clicked and the work sheet "2B Board Items" will accumulate the copy and pasted data. This works for the 1st time around, but when week 2 comes and some of the typed inputs change then i need the macro to clear all the data except for any potential duplicates that may've resulted from the input section.

Not sure what the best approach is to this problem. Column D was chosen for both because it's the only column that can consistently prove whether it's a duplicate.

 

[JLGWhiz]

I understant that you want to only clear contents, and not delete and that you want it incorporated into this procedure.
How about question 4? Which columns do you want checked for duplicates.

 

[pahickham]

If the values of the cells in column D match then it's a duplicate.

 

[JLGWhiz]

see if this does what you want.

Private Sub CommandButton2_Click()
Dim c As Range, a As Long, fn As Range
a = Worksheets("All Data").Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In Sheets("All Data").Range("D2", Sheets("All Data").Cells(Rows.Count, 4).End(xlUp))
        Set fn = Sheets("2B Board Items").Range("D:D").Find(c.Value, , xlValues)
            If fn Is Nothing Then
                c.EntireRow.ClearContents
            End If
           Set fn = Nothing
    Next
    For i = 2 To a
        If Worksheets("All Data").Cells(i, 8).Value > 0 Then
            Worksheets("All Data").Rows(i).Copy Worksheets("2B Board Items") _
            .Cells(Rows.Count, 1).End(xlUp)()
        End If
        Application.CutCopyMode = False
    Next
Worksheets("2B Board Items").Activate
ThisWorkbook.Worksheets("2B Board Items").Cells(1, 1).Select
End Sub

 

[pahickham]

I apologize I fed you some faulty information.

So as discussed before when I click the command button in "Equipment Numbers" sheet the first time the rows in which H > 0 for "All Data" are copied over to
"2B Board Items"

When the inputs for the 3rd sheet "Equipment Numbers" are changed then the new values for "All Data" in column H are now automatically re-evaluated returning a value of 0 or >0. Some values may stay the same, H>0, because the original input was still left on "2B Board Items" list, while others have now become 0 on the "All Data" list since the input has been removed.

I relayed the wrong info to you and for that I apologize. I'm greatly appreciative for all your help.

The idea is for the Macro to edit the "2B Board List" solely. The biggest issue I'm having is when I have an Input stay the same on the "Equipment Numbers" sheet 2x or 3x in a row is I cant have any data edited because granted some information is copied from "All Data" but there are also other columns to the right of those copied cells that have hand typed information in regards to the equipment that I wouldn't want deleted every time I click the Command Button.

Sorry for the elaborate and most likely poor explanation. I'll make some sample sheets the explain start and finish to help explain.

 

[pahickham]

So here's a reference. The green areas of the "2B Board Sheets" are where data is manually inputted. It would be ideal if that area remained the same if a duplicate option was presented.

app.box.com shared files:

Box

app.box.com

 

[JLGWhiz]

That didn't help. There are no column letters or row numbers referenced on any of the sheets you posted but I assume they all begin in column A for sheet 'All Data'. All I need to know is which column do you want to evelaute, and what do you want to evaluate it for. ie. Column D for for cells not blank, or Column H for cells > 0. Please answer the question specifically. I am not interested in how you conduct business at this point. I just need to know where on the sheet to look for the criteria and what the criteria really is.

 

[pahickham]

Did you dowload the file and open with excel, because otherwise it;s completely unreadable.