Customer Copy/ Paste in VBA

[Panik]

Hi,

On a Worksheet_Activate event I am looking for a way to copy all items found in a range $C$4:$I$100 (I'll name the range "Parts") to a different worksheet "Part Text", ignoring blanks and duplicates. I would like all items found in the range copied (ignoring blanks and duplicates that may exist in the copied text), but only paste to the "Part Text" worksheet if they do not already exist in the "Part Text" worksheet. I would like them pasted in Column "A" of the "Part Text" worksheet even though they are being copied from multiple columns and rows in the "Parts" range. I would like this to automatically happen when the "Part Text" worksheet is selected (Worksheet_Activate).

The idea being that when a new part is added to the "Parts" range. If it does not already exist in the "Part Text" worksheet, it will be added to the end of the list in Column "A".

Is this possible? I appreciate the help.

Jim

 

[My Aswer Is This]

Why not try this:

When you enter a new part on sheets named Master
The range A to I of that row is copied to first empty row on sheets named

Part Text

This would assume a part number would be in column A of both sheets that we could search for in column A of sheet named Part Text

If the part number is not found then the Range A to I would be copied to sheet Parts Text.


​

 

[Panik]

I worked out the below, it is working, but not sure if a better way exists. Just thought I would post it for others if it would come up.

Private Sub Worksheet_Activate()


    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet


    Set copySheet = Worksheets("PumpInfo")
    Set pasteSheet = Worksheets("PartText")


    copySheet.Range("OH_ALL").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
    copySheet.Range("bb_1or3").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


    copySheet.Range("bb_2").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


    copySheet.Range("bb_4").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


    copySheet.Range("bb_5").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
    copySheet.Range("VS_ALL").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


    copySheet.Range("OTHER").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    Worksheets("PartText").Range("A:A").Select
    ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo


    Range("A1").Select

End Sub

 

[My Aswer Is This]

The problem I see with using named ranges is what happens when you want to expand the range.
I guess you did not like my earlier ideal.

And I see nothing in your script that does this:

I would like all items found in the range copied (ignoring blanks and duplicates that may exist in the copied text), but only paste to the "Part Text" worksheet if they do not already exist in the "Part Text" worksheet.​

 

[Panik]

Yeah, I handled it a little differently. But I think I am getting to the same goal. The way I sliced it was to copy everything from the ranges and paste it at the bottom of the existing list in the "PartText" column. Then, the last line of code is to "RemoveDuplicates". Since everything that was already in the column would be duplicated below it, removing all these duplicates only leaves the new items on the bottom.

As for the ranges, I created dynamic ranges that grow as I add items. So they are basically unlimited in length from what I can tell.

 

[My Aswer Is This]

Glad you came up with your way. So I assume you need no help.
Your original question was:

I worked out the below, it is working, but not sure if a better way exists.
But sounds like your now happy with your way. Take care​

Yeah, I handled it a little differently. But I think I am getting to the same goal. The way I sliced it was to copy everything from the ranges and paste it at the bottom of the existing list in the "PartText" column. Then, the last line of code is to "RemoveDuplicates". Since everything that was already in the column would be duplicated below it, removing all these duplicates only leaves the new items on the bottom.

As for the ranges, I created dynamic ranges that grow as I add items. So they are basically unlimited in length from what I can tell.

 

[Panik]

Well, I'm not sure I am happy. I'm not sure what problems may present, but if they do, If I can't figure it out, I know I have some help! Thanks!!!