Consulting

Results 1 to 8 of 8

Thread: copy and paste value with condition

  1. #1
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    149
    Location

    copy and paste value with condition

    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.
    Attached Files Attached Files

  2. #2
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    149
    Location
    Any one please help me

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    779
    Location
    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

  4. #4
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    149
    Location
    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
    Last edited by elsuji; 05-30-2020 at 10:59 AM.

  5. #5
    VBAX Expert
    Joined
    Sep 2016
    Posts
    779
    Location
    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
    Last edited by mana; 05-30-2020 at 06:53 PM.

  6. #6
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    149
    Location
    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





  7. #7
    VBAX Expert
    Joined
    Sep 2016
    Posts
    779
    Location
    I can't undersand.

    Quote Originally Posted by elsuji View Post

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

  8. #8
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    149
    Location
    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
    Last edited by elsuji; 05-31-2020 at 07:31 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •