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