VBA Copy and Paste With Duplicates

pahickham

New Member
Joined
Jun 5, 2017
Messages
36
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

VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,274
Office Version
2013
Platform
Windows
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

New Member
Joined
Jun 5, 2017
Messages
36
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,274
Office Version
2013
Platform
Windows
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

New Member
Joined
Jun 5, 2017
Messages
36
If the values of the cells in column D match then it's a duplicate.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,274
Office Version
2013
Platform
Windows
see if this does what you want.

Code:
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

New Member
Joined
Jun 5, 2017
Messages
36
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

New Member
Joined
Jun 5, 2017
Messages
36
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:

 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,274
Office Version
2013
Platform
Windows
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

New Member
Joined
Jun 5, 2017
Messages
36
Did you dowload the file and open with excel, because otherwise it;s completely unreadable.
 

Forum statistics

Threads
1,081,963
Messages
5,362,431
Members
400,676
Latest member
kazoli

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top