I Believe this is what you were talking about. I worked this out today, and it appears to be doing the job. I'm not sure if it is the best way to do it.
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
Bookmarks