Due to organization restriction not able to share the workflow. I have a trial balance for 10 legal entites spread across 5 countries. I am generating reports based on the country names. In each report there are various tabs. In one tab i am creating a pivot using cross tab where i need the sum at legal entity level however my raw daya contains all 10 entity hence when i am generating the reports using render tool in country one report i am getting all 10 column where 8 column dont have any value rather then header. Not sure a batch macro or what can help. Have tried using data cleansing, dynamic select no clue so far, appreciate if anyone can help.
Your thinking sounds correct. Dynamic select and data cleansing are your best options to use for this within a batch macro. Are you able to provide sample data (just the headers and some fake values)? Without anything data to work with it is difficult to give you a precise solution.