I have three files...
One is the Suspense Count macro...that contains the two macro's I have been working with. Module 1 is the original code that is now obsolute because the file it was referencing has now changed. Module 2 is the updated code that I have been working with and that has been giving me nightmares. I am simply trying to replicate the same formatting processes for Module1 as in Module 2 but have run into several speed bumps...
1) When the code for Module 2 runs straight through (which is seldom) the end result should be that the "Calc sheet" populates with the expression [=SUM(IF('Suspense Exceptions Results'!$A$2:$A$4523=$C5,
'Suspense Exceptions Results'!D$2:D$4523,0))] for columns (AB:AI) The expression does not seem to be doing this properly and I know it is correct (I basically just copied it from the pre-existing expression used to run "Module1" in the "Calc sheet". My hunch is that the placement in "Module2" of my autofiltering is the cause of the problem. I also think that the "Product", "Department" and "Account Name" columns in the "Suspense Exceptions Results" sheet may not be copying the formulas in their respective cell correctly.
Module 2...
1) is basically opening up the Suspense Account Template
2) goes back to the "Suspense Account Macro" file in "Table of Accounts" sheet.
3) copy's formula in code in new column to left of "acct#" column so it is less one digit to the right and then names the new column "Account" for vlookup purposes.
4) Activates "Registry" sheet in "Suspense Count macro" file to get path to open file to be uploaded into "Suspense Count template" file called "table_Suspense_Exceptions
_Results43
516".
5) Moves "Exported Sheet" sheet from "table_Suspense_Exceptions
_Results43
516" file to Suspense Count Template" file.
6) Goes to "Calc" sheet and copies and formats date.
7) Goes to "Exported Sheet" sheet and removes unnecessary fields (really should only have "Account", "CCY", "Age" left).
Okay things get a little cagey here because it's doing things with the whole Columns ("A").Value function that I don't necessarily understand. This line of code was done by one of you expert guys...using a variable such as "iLastRow" worked fine for me but that's another story. Infact I don't think that the Columns("A").Value = .Columns("A").Value works in my code effectively because it does not stop the pasting of cells at the last row. It just goes down all the way to row 65***. Not good. It has to go down only to the last value in a particular row because it is causing me headches when i try to autofilter.
7b) It also does a series of calculations and (autofiltering which I may have placed prematurely.) which is probably why the end result of the "Calc" formula cannot calculate the date being referenced from the "Suspense Exceptions Results" correctly.
8) Adds/creates new sheet named "Temp2" where it pastes the final format of the "Exported Sheet" sheet. It is then subtotaled by Account and three new columns (Product/Department/Accoun
t Names) are created to do vlookups of same accounts being reference in "Suspense Account Macro" under "Table of Accounts" sheet (updated earlier in code in step 3).
9) Goes back into "Suspense Exceptions Results" sheet paste formatted "Temp2" file into sheet. Remove total and sort by Product and Department (which does not work in my code "Module2" suspect this has something to do with the autofiltering placement in my code.
10) "Temp2" sheet replace Total with " ". which allows vlookups fields (Product/Department/Accoun
t Name) to populate. Auto filter for "#N/A".
The rest is self explanatory...probably all of this is...but I'm desperate for help so...you get everything I think can be of assistance.
Please let me know where to send files and hopefully my nightmare can be over soon...
