Trouble with Macro and Formula


I had some help with this before and made some changes to the file. It's still not doing quite what I need the file to do.

In the post review tab, I want everything to copy over to the "reviews" tab. There is a formula in cell "AD" that I can't get to copy correctly.

In short, I want to go to "Associate Review" section and type in a name and have it auto populate the most recent 20 entries from the "Reviews" Sheet.

I also can't get the formulas in the "Monthly" and "Weekly" sections to work.


Who is Participating?
I have made a small change to your macro code to double up the quotes as they were inside a string:
    Worksheets("Reviews").Range("X2").PasteSpecial Paste:=xlPasteValues, Transpose:=False, SkipBlanks:=False
    Worksheets("Reviews").Range("Z2").Formula = "=VLOOKUP(A2,'Associate Team'!$A:$C,2,0)"
    Worksheets("Reviews").Range("Y2").Formula = "=VLOOKUP(A2,'Associate Team'!$A:$C,3,0)"
    Worksheets("Reviews").Range("AA2").Value = Date
    Worksheets("Reviews").Range("AB2").Formula = "=MONTH(AA2)"
    Worksheets("Reviews").Range("AC2").Formula = "=WEEKNUM(AA2)"
    ' Changed this line ============================================================
    Worksheets("Reviews").Range("AD2").Formula = "=A2&"":""&COUNTIF($A$1:A2,A2)"
    Application.CutCopyMode = False
    Call ClearReview
    Worksheets("Post Reviews").Range("C17").Formula = "Optional"
    Worksheets("Post Reviews").Range("C2").Formula = "Optional"
    Worksheets("Post Reviews").Range("A4").Formula = "Optional"

Open in new window

I have also changed the formulas on the Associate review tab to use the INDEX function, and they now work correctly.

I'm not quite clear what the formulas on the Monthly and Weekly tabs are supposed to be doing - they have lots of REF! errors, presumably because you have deleted rows they originally referred to. As that is quite separate from the things I fixed, can I suggest that you raise a new question, with a full explanation of what you want them to do - it's better not to try to do multiple different issues in the same thread.

nomiosAuthor Commented:
Thanks. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.