copy and paste value with condition

[elsuji]

DEar Team,

I am copy and paste values from multiple workbook to master book. For that i am using the bellow code.

Sub CopyData() Application.ScreenUpdating = False
 Dim SrcWkb As Workbook, SrcWks As Worksheet, DstWkb As Workbook, DstWks As Worksheet, Val As String
 Dim i As Long, j, v1, v2, RngList As Object, strExtension As String
 Dim sFolderName As String, sFolderPath As String
 
 Set DstWks = ThisWorkbook.Sheets("Dest")
 
 v1 = DstWks.Range("C3", DstWks.Range("C" & Rows.Count).End(xlUp)).Value
 'strExtension = Dir(ThisWorkbook.Path & "\" & "*.xlsx")
    sFolderPath = Environ("USERPROFILE") & "\Desktop\KRA SUmmary\"
    sFolderName = Format(Date, "yyyy") & "\" & Format(DateAdd("M", -1, Now), "mmmm yyyy") & "\"
    strExtension = Dir(sFolderPath & sFolderName & "*.xlsx*")
    
 Do While strExtension <> ""
 Set SrcWkb = Workbooks.Open(sFolderPath & sFolderName & strExtension)
 v2 = Sheets(1).Range("C3", Sheets(1).Range("C" & Rows.Count).End(xlUp)).Resize(, 14).Value
 
 Set RngList = CreateObject("Scripting.Dictionary")
 For i = 1 To UBound(v2, 1)
 Val = v2(i, 1)
 If Not RngList.Exists(Val) Then
 RngList.Add Key:=Val, Item:=i
 End If
 Next i
 For i = 1 To UBound(v1, 1)
 Val = v1(i, 1)
 If RngList.Exists(Val) Then
 DstWks.Cells(i + 2, 5).Resize(, 12) = Array(v2(RngList(Val), 3), v2(RngList(Val), 4), v2(RngList(Val), 5), v2(RngList(Val), 6) _
 , v2(RngList(Val), 7), v2(RngList(Val), 8), v2(RngList(Val), 9), v2(RngList(Val), 10), v2(RngList(Val), 11) _
 , v2(RngList(Val), 12), v2(RngList(Val), 13), v2(RngList(Val), 14))
 End If
 Next i
 SrcWkb.Close False
 strExtension = Dir
 Loop
 Application.ScreenUpdating = True
End Sub

In my master sheet E5:P5 months are available. Also same months available on my source work books. The above code is copy and paste all the available datas. But y condition is when ever i am run the code it should copy the previous month of current month datas only from source file and paste to same previous month column on master sheet.

For example, Current month is May. I i run the code it should copy April month data from my source file and master file it should paste on April month.

Can any one help me to solve this.

i am attaching my files here for your reference.

[elsuji]

Any one please help me

[mana]

Dim m As Long
m = Month(WorksheetFunction.EDate(Date, -1))
Set RngList = CreateObject("Scripting.Dictionary")

Do While strExtension <> ""
    Set SrcWkb = Workbooks.Open(sFolderPath & sFolderName & strExtension)
    v2 = Sheets(1).Range("C3", Sheets(1).Range("C" & Rows.Count).End(xlUp)).Value
    RngList.RemoveAll
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1)
        If Not RngList.Exists(Val) Then
            RngList.Add Key:=Val, Item:=i
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1)
        If RngList.Exists(Val) Then
            DstWks.Cells(i + 2, m + 4) = Sheets(1).Cells(RngList(Val) + 2, m + 4)
        End If
    Next i
    SrcWkb.Close False
    strExtension = Dir
Loop

[elsuji]

Dear Mana,

Thanks for your reply.

I checked your code. It is copying only one column values.

I changed the month manually for checking purpose and run the code, is not working

I think in code it is defined only for the April month. Because of that it is not copy paste the other month values.

Code: DstWks.Cells(i + 2, m + 4) = Sheets(1).Cells(RngList(Val) + 2, m + 4)

But i want it should check the current month and copy paste the previous month data for all the months.

Can you please check and update me

[mana]

Set RngList = CreateObject("Scripting.Dictionary")


Do While strExtension <> ""
    Set SrcWkb = Workbooks.Open(sFolderPath & sFolderName & strExtension)
    v2 = Sheets(1).Range("C3", Sheets(1).Range("C" & Rows.Count).End(xlUp)).Value
    RngList.RemoveAll
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1)
        If Not RngList.Exists(Val) Then
            RngList.Add Key:=Val, Item:=i
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1)
        If RngList.Exists(Val) Then
             Sheets(1).Cells(RngList(Val) + 2, 5).Resize(, 12).Copy
             DstWks.Cells(i + 2, 5).PasteSpecial xlPasteValues, , True
        End If
    Next i
    SrcWkb.Close False
    strExtension = Dir
Loop

[elsuji]

Dear Mana,


I checked your code. It is copying all the values.


Let me explain you;


Every month i will receive the updated files(Source files) from all the region and i will consolidate that files to single file(destination file).


For example, April month data i will receive on or before 15th of May month. When i am press the copy data button it should copy only April 20 values from source file and paste to April 20 on destination file. it wont do any changes on Jan to March value. If there is any blank on Jan to March let it be blank.


Again May month data i will receive on or before 15th of June month. When i am press the copy data button it should copy copy May 20 values from source file and paste to May 20 on destination file. it wont do any changes on Jan to April value. If there is any blank on Jan to April let it be blank.


Can you please modify the code as per my requirement

• 
  

[mana]

I can't undersand.

But i want it should check the current month and copy paste the previous month data for all the months.

[elsuji]

Your new code is copying the values of all the months. It should copy only the previous month value only monthly wise when I am running the code.

Month is in the range of E2:P2 of both the source and destination files. But this is not declared any where in the code. Then how the code will check the previous month value and copy yo destination sheet