Apologies for both the lack of clarity and time gap. This is my first time using a forum for help like this, so am a newbie at this and the headache of this has grown on me for over 2-months of approaching it.
To explain the situation at the moment. Right now, we are pulling from an online program to fill a large pivot table, that has multiple pivot tables within it, that we look up, enter the pivot tables, and then get our results. The issue is that these pivot tables are not just one, but one for every client, with multiple factors that change the values and what/how it is calculating from inputs changing to the number of days in a month, all of which we must edit every time there is a change just to pull the data. Even more, the program we are looking up the data in to then copy to the pivot tables, has an excel export mode. All current pivot tables DO NOT MESH NOR COOPERATE with the Exportable Data. I.E. The Pivot tables, although being used currently, are only increasing the amount of time to get the data and are making things harder, rather than faster. So, my goal is to do away with the manual entering pivot tables, and format a VBA program that can do all the work for us from the exported Excel file, then be uploaded to Power BI. I repeat, I need to do this through VBA, not correct a past made pivot tables.
So, for what I have been attempting to do. I have cleaned and included an example file this time. I have the Modules formatted in the last format I was trying to get it to work in. I have tried doing all of a client’s counts in one Sub, I.E. I tried to have it count all of John Doe’s Yes, No, Refused, and Not Applicable results in one sub and then place the data in John Doe’s Client sheet “JD”. When that failed, I formatted like this to do each result, yes/no/Refused/Not Applicable for John Doe was its own Sub. The issue I have discovered is that VBA drops the client’s name as a requirement for the counts of Yes, No, etc. once it sees the name in that column and defaults to a basic CountIF function for all data.
Now, the main reason for the shift to VBA is that there is this hurdle to meet, and then more to evolve on this VBA program. After I have it to where it can check for Client name then count correlating results, I need to also program in even more requirements that must be met to validate the counting. To give an example, from my example file, there is only one Goal type, in Outings. There will be more goal types, and I will in the future need it to differentiate between Yes’s when John Doe went on an Outings goal versus a Health goal. So, I will need to have a system that can have two or more prerequisites being met before it counts that data.
Reply With Quote