VBA code tweak needed (To cut and paste from one file to another and then conditional formatting)

[Brandonfromsingapore]

Hello Forum! I have a problem and was hoping to ask the forum for help!
Basically, I have two excel files in csv format (plain format). I want to carry out the follow operations


The user (anyone in my project team) will create a folder on their computer desktop. The name of the folder is called “Fruits Audit”. Is there a way to ensure that this is not case sensitive? I hope the VBA will still work if the file was unintentionally named wrongly, such as "fruits audit". It should not be case sensitive. This would be the best option. Thank you.

Inside this folder which is on the desktop, the person will place two files in them. One file is called

Name of first file is “*^FRUITS.csv” (the name of the file will always end in "^FRUITS.csv" so this is why I am using a wildcard here.

Name of the other second file file is “*PROCESSED.csv”

The person then open the first file and activate the vba macro. Running the macro by opening the first file.
This is the actions to be done:-
Clear all contents in column (C:D). I do not need any data in these 2 columns. I just need to have blanks / empty cells in these two columsn.
Then go to cell E1. Then

Range("E1").Select
    Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

I was hoping that this above code is to select and copy from cell E1 and proceed to the right until the last cell that had data.

Then next step is

Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Columns("E:HZ").Select
Selection.Delete Shift:=xlToLeft

After the above operation is done, I need to continue...
Now, open the other second file in the same folder.

Do you know if I can use this code?

strPath = ActiveWorkbook.Path & "\"
 
strFile = Dir(strPath & "*PROCESSED.csv")

Thendelete cell A1 and also delete cell D1

next

Range("A1").Select
    Selection.ClearContents
    Range("D1").Select
    Selection.ClearContents
    Columns("A:A").Select
    ActiveSheet.Range("$A$1:$A$10000").RemoveDuplicates Columns:=1, Header:=xlNo
    Columns("D:D").Select
    ActiveSheet.Range("$D$1:$D$10000").RemoveDuplicates Columns:=1, Header:=xlNo
    Range("A:A,D:D").Select
    Range("D1").Activate
    Selection.Copy

Then next operation is

Activate again File one (ie the file name is “*^FRUITS.csv”)
Go to cell G1 and "paste"

Then next operation is

Cells.Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select

Then message box will appear

MsgBox ("The operation has been completed! Please check the cells which are not highlighted!")

Thank you so much! I am very new to VBA codes so I only know some but don't know the others and I need help . Thank you forum!

 

[pbornemeier]

I think this will do what you are asking for. Be sure to test on a copy of your data.

This code should be placed in a standard module in a new workbook. Put a button the worksheet in the new workbook that starts the procedure.

Option Explicit

Sub CheckNoDupeFruit()

    Dim sFruitsFileName As String
    Dim sProcessedFileName As String
    Dim sDesktopPathName As String
    Dim sDummy As String
    
    sDesktopPathName = Environ("userprofile") & "\Desktop\"     'Get the path to the desktop. add a "\"
    sFruitsFileName = Dir(sDesktopPathName & "*^FRUITS.csv")  'The Dir command is not case sensitive
    If Dir <> "" Then
        MsgBox "There is more than filename ending in '^FRUITS.csv' on the desktop. Fix this problem then continue"
        GoTo End_Sub
    End If
    
    sProcessedFileName = Dir(sDesktopPathName & "*PROCESSED.csv")  'The Dir command is not case sensitive
    If Dir <> "" Then
        MsgBox "There is more than filename ending in 'PROCESSED.csv' on the desktop. Fix this problem then continue"
        GoTo End_Sub
    End If
    
    Workbooks.Open sDesktopPathName & sFruitsFileName    'sFruitsFileName is the active workbook

    Range("C:D").Columns.Delete     'Delete columns C & D in sFruitsFileName
                                    'The old column E in now column C
                                    
    Range(Range("E1"), Range("E1").SpecialCells(xlLastCell)).Columns.Delete
                                    
    'Don't need code block 2 from OP, because columns were 'moved' when D&D were deleted
    
    Workbooks.Open sDesktopPathName & sProcessedFileName     'sProcessedFileName is the active workbook

    'act on sProcessedFileName as described in Block 4 of OP code
    Range("A1").ClearContents
    Range("D1").ClearContents
    Columns("A:A").Range("$A$1:$A$10000").RemoveDuplicates Columns:=1, Header:=xlNo
    Columns("D:D").Range("$D$1:$D$10000").RemoveDuplicates Columns:=1, Header:=xlNo
    
    'Copy from sProcessedFileName to sFruitsFileName
    Range("A1:A10000,D1:D10000").Copy Destination:=Workbooks(sFruitsFileName).Worksheets(1).Range("G1")
    
    'act on sFruitsFileName as described in Block 5 of OP code
    Workbooks(sFruitsFileName).Activate
    With ActiveSheet.UsedRange.Cells
        .FormatConditions.AddUniqueValues
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).DupeUnique = xlDuplicate
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    Range("A1").Select

    MsgBox ("The operation has been completed! Please check the cells which are not highlighted!")

End_Sub:

End Sub