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

Joined
Jan 5, 2012
Messages
38
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
Code:
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

Code:
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?
Code:
strPath = ActiveWorkbook.Path & "\"
 
strFile = Dir(strPath & "*PROCESSED.csv")

Thendelete cell A1 and also delete cell D1

next

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

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

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,893
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.
Code:
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
 

Forum statistics

Threads
1,143,654
Messages
5,720,109
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top