elsuji
05-29-2020, 11:27 AM
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.
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.