Posts by elsuji

    I tried this following doe for rank. But all the range R3:R & last row it is printing same value.


    Code
    1. Dim lastrow As Long
    2. With DstWks
    3. lastrow = Cells(Cells.Rows.Count, "Q").End(xlUp).row
    4. .Range("R3:R" & lastrow).Value = WorksheetFunction.Rank(.Range("Q3"), Range("$Q$3:$Q" & lastrow), 0)
    5. End With

    Van you please tell me what is the mistake on this code

    Sorry. It is my mistake. I forget to upload link.


    I used the following code.


    This working. But when i am selecting any cell on that range, the formula is displaying. I don't want to display the formula.

    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

    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.

    Dear Team,


    I am entering my engineers mark on columns from E to P. whenever I am entering values it want to calculate average of column E to P and the average value should update in column Q.


    Also i want to calculate the no.of position for that average value.


    I attached my sample file here for your reference.


    Kindly help me to do this

    Files

    • Sample.xlsm

      (11.28 kB, downloaded 21 times, last: )

    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

    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

    Dear Team,


    I had created pivot table . On my pivot table in VALUES field i add column A Which is heading "SOURCE". In column A the followings are available (CALL, NONE, TIME, VISIT)


    Once i add the SOURCE in pivot table it will count and display the number. But i want to display the name instead of no.


    Can any one help me how to display the names on the pivot table


    Also explain me how to do this


    I am attaching my file here for your reference

    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