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.