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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,061
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
12,061
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
12,061
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
12,061
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,602
Messages
5,487,806
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top