The code isn't working

[MazExpress]

I have a monthly sheet (Aug. Profits) that has 2 columns of concern, F & G. How to copy the values in column G to another workbook (Actual) in the column of the current month?
Knowing that:
1- The order of names in the first sheet is different from month to another.
2- Sometimes there are no names available in column F, so the corresponding values are useless.
3- Some names and values maybe unwanted. In other words, I want to only copy the values in column G which have names in the second sheet.

A very helpful member of the forum had suggested this code:

Sub CopyValues()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, Val As String, arr1 As Variant, arr2 As Variant, i As Long, fnd As Range
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = Workbooks("Actual.xlsx").Sheets("Sheet1")
    Set fnd = WS2.Rows(1).Find(Left(MonthName(Month(Date)), 3))
    If Not fnd Is Nothing Then
        arr2 = WS2.Range("A2", WS2.Range("A" & WS2.Rows.Count).End(xlUp))
        arr1 = WS1.Range("F2", WS1.Range("F" & WS1.Rows.Count).End(xlUp)).Resize(, 2).Value
        Set RngList = CreateObject("Scripting.Dictionary")
        For i = LBound(arr1) To UBound(arr1)
            Val = arr1(i, 1)
            If Val <> "" Then
                RngList.Add Key:=Val, Item:=arr1(i, 2)
            End If
        Next i
        For i = LBound(arr2) To UBound(arr2)
            Val = arr2(i, 1)
            If RngList.exists(Val) Then
                WS2.Cells(i + 1, fnd.Column) = RngList(Val)
            End If
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

The code is working properly with him and fulfilling my needs. When copying the same code to a new module on my setup, it didn't work at all !! N.B Both of us are using Windows 10 and Office 2010.

Any help ??

 

[jasonb75]

'Not working' is a very ambiguous term when it comes to excel. Does nothing? Does the wrong thing? If so, what is it doing wrong? Runtime error? If so what error code and which line?

Is "Sheet1" the correct sheet name in both workbooks?

 

[MazExpress]

I mean it does nothing. Everything stays as it is. No changes and no errors.

Yes, "Sheet1" is the correct sheet name in both workbooks.

 

[jasonb75]

Works fine for me, I had to use the Aug sheet from @mumps post in the original thread as the one that you attached here has an invalid file extension.
Try this test code to see if it is picking up the august column correctly. It will either show a message box saying $I$1 or a runtime error.

Sub findmonth()
MsgBox Workbooks("Actual.xlsx").Sheets("Sheet1").Rows(1).Find(Left(MonthName(Month(Date)), 3)).Address
End Sub

I'm not entirely sure what will happen if the scripting dictionary is not available to you, although I would expect an error rather than simply nothing.

 

[MazExpress]

 

[jasonb75]

That means that it's not finding Aug in the cell, best guess is that your sheet contains dates formatted as MMM while the code is looking for a text string representing the month.

If you double click into I1 (or whichever cell contains 'Aug' in your sheet) does it stay as Aug, or does it change to a date in the format of 01/02/2020?

 

[MazExpress]

It stays as Aug.

 

[Trevor G]

Have you set the Office Scripting on the other machine as you mentioned when you copy the module to your setup. In VBA Select the Tools Menu and Reference and search down for Microsoft Scripting Runtime

 

[Fluff]

That code uses late binding, so no references are needed.

@MazExpress
Does the code work for you on the test files?

 

[MazExpress]

I've checked that box, but nothing different has happened when I ran the macro again! No changes and no errors.