MazExpress
Board Regular
- Joined
- Aug 5, 2020
- Messages
- 56
- Office Version
- 2010
- Platform
- Windows
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:
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 ??
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:
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 ??