Macro to sort and insert text based on a cell's value, and other small things.

Workbook attached.

First thing we do is remove duplicates only using column "A"
Then we create a column "G" titled "Days Since Payment" and insert the formula "=today()-XX"
Then we create a column "H" titled "Group"
"H" has the groups, "0-33","34-60","61+","Never". Whichever group the value of "G" falls in between is what goes into "H"
Then column G is column by smallest - largest.

This worksheet will be hundreds of rows deep.

Then we insert a pivot table with Account Queue Name/Group in the Rows and Count of Days in values.

I could record a macro that does most of these, other than the grouping I think, but with the # of rows involved, I figure i'll bog my computer with just a recorded macro.

Workbook has three sheets. a "raw" where you can see what we start with and should be able to track with my steps, the the Finished sheet that shows the results and finally the pivot table.

Thank you so much for doing that. I do receive a #NAME? error when I attempted to run it on my example workbook. It does stop at the end of the last row, but has that error

So WTH! lol

So I modified it a little bit we don't have to worry about the sheet name. But i think that does it! As far as the pivot, I did a recorded macro and tried to work with another macro to combine them. One issue I know is the "source data" wouldn't be DCMR. I would like it to just be the active worksheet.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If I adjust the vba at all, even undo back, I get the #NAME? error

So Mike, this is what I have, and it works perfectly when I run it inside your workbook. But if I move/copy that raw sheet out of yours, then this macro and yours fails.

Please Login or Register  to view this content.

Sorry mike, I didn't see your response. It is the same error as my first post. #Name? in the Group column

It isn't an error with the code itself. The error is what shows up in the group column. How down that column it will have the #NAME?

The macro runs without issue.

Hey Mike, ever cell has it in the column, but it does stop at the last row. And it should be the value based on the group. You wrote the macro for it, it is the 0-33, blah, 61+ that is put into that column. It is the same error as my post #3 and the other thread that was closed I started that you comment on. That has the two workbooks for easy comparison and the better explanation too.

Try

Please Login or Register  to view this content.

Subscript out of range.

Please Login or Register  to view this content.

Seems I have to create another sheet and name it finished?

Is that easier than fixing the one mike helped with? It is setup for autofit and does the pivot and everything.

I have it saved into PERSONAL.XLSB and I have ran the code from that location on both and resulted in the aforementioned issues.

But I have it under "MODULES" I do insert>module

Originally Posted by taylorsm

I have it saved into PERSONAL.XLSB ....

Please Login or Register  to view this content.