copy datas using vlookup from another workbook

  • Dear Roy,


    I add new button for download the files from mail and create folder and save all files month wise.


    For consolidating it, the files to be taken from the current month folder.


    I am attaching new file here.


    Please check the attached file and help me to consolidate the list

    Files

  • My requirement is


    when ever i am run the code it should copy the previous month values of current month from source file and paste that to same previous month column on Destination sheet.

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

    And for checking the source file i had modified code to

    Code
    1. sFolderPath = Environ("USERPROFILE") & "\Desktop\KRA SUmmary\"
    2. sFolderName = Format(Date, "yyyy") & "\" & Format(DateAdd("M", -1, Now), "mmmm yyyy") & "\"
    3. strExtension = Dir(sFolderPath & sFolderName & "*.xlsx*")


    My source files are downloaded from outlook mail. for that i had created code already. Please look at the attachment.


    Kindly do the modification as per my requirement

    Files

  • My code simply copies all the data to be faster. That also allows for any changes that might have occurred.


    You stated that the files were all in the same folder when I asked earlier so your code above is unnecessary.

  • Try this.


    If you don't have all the files in the same folder as the main workbook then you will need a folder picker or maybe make sure the folder is named for the month

  • Dear Roy,


    I modified Mr.Mumps code as per the requirement and checked. Bellow is the modified code


    It is copying only the previous month data's of current month.


    But for checking purpose changed the month (before and after the current month) manually and run the code, is not working.


    Can you please tell me where is the mistake on this code

  • I'm not checking other peoples' code. That is up to the author. You haven't even mentioned my code.


    I've amended my code slightly because it looks like you intend having data in columns Q & R. I have also made it copy only the data for the previous month. You can change the path using your code.

  • Dear Roy,


    I checked your code. It is copying last updated column on source file and in destination file it is paste the last empty column. in that case it will keep on filling same values again and again for all the columns until i update the next data on my source file.


    But my requirement is first it should check the heading which column the previous month is available and copy paste values on that particular month. So the values are not repeated on my destination file.


    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.

  • Did you try the macro I suggested in Post #4?

    Dear Mumps ,


    I tried your macro and modified little bit. Bellow is the modified code.


    It is copying only the previous month data's (April Month) of current month.


    But for checking purpose changed the month (before and after the current month) manually and run the code, is not working.


    Can you please tell me where is the mistake and what is the correction have to do on this code