I had this question after viewing EXCEL VBA: Help to understand a previous given solution
Please apply the VBA code solution to this question into uploaded excel file labeled "FILE-1".
Using similar methodology used by Roy Cox in attached file #2 (usage of dynamic rage solution) which contains a macro called "ConvertData", with no need of further user interaction more than just simply pressing a button which triggers the macro, then the following should happen:
1. In the tab "Table Data" (note that this tab is hidden, and would like to keep it so), it:
1.(a) Deletes all data below headers "Project Commment, Raw Data", then
1.(b) updates the table below headers "Project Commment, Raw Data", with the data from "Input data sheet".
2. Update excel tab "Journal update" with the name of the user shown to be using the excel file at the time the macro botton is pressed, keeping a journal of all the times this has been updated and keep adding in the first row, the next update time which this macro was updated.
3. "Refresh" all
pivot tables in the worksheet.
3.(a) Will this macro as you send it now "refresh" even new pivot tables that are added to the excel file without needing to modify the macro you propose?
3.(b) in the column label of the pivot table (as an example, see cell B13 of excel sheet "Report-All activities"), hide all weeks which are older then two week from current week number (week starting on Mondays) - meaning unselect all number which are less then. If it helps, I have current week calculated in cell R3 of excel sheet "Report-All activities" (but you would need to add a range in case this figure is moved somewhere else). Please apply this to pivot table in excel sheets: (a) "Report-All activities", and (b) sheet "Report-Per activity".
3.(c) then, for sheets "Report-All activities", and "Report-Per activity":
3.(c).1 highlight the entire column, which corresponds to the current week. I have added a "hidden" helper row for this part, in row 1 of sheets "Report-All activities", and "Report-Per activity".
4. To easily confirm to the user that the macro has run, please add date a stamp in cell A1 of sheet "Input Data Sheet stating "The reports have been last updated: " name of the user and plust the date/time.