I have a macro that just copies and pastes from a sheet into a destination sheet. The data sheet changes (we get a report each week) but the destination sheet is always the same. the two sheets can be saved in the same working folder.
the format of the name of the excel sheet that is changing is XXX version_XXX XXXXXX XXXXX FY2022 XXXX WE 11-10-2021. The date on this file name is always the previous Monday which is when the report is run.
- I am thinking of just using a text box, to input the new sheet name (data sheet). Is this the best way to do this or is there a more automated way. -considering the date is always the previous month is there a way to just get the previous month and input into the macro? note the date format below.
- Is there a way for the copy, paste to occur on the same sheet, ie just copy paste over old data, instead of creating a new sheet as the macro below does.
The below is my current starting macro. This is what i have been able to do using record macro.
Sub Macro1() 'Macro1 Macro Sheets. Add After:=ActiveSheet Windows ("Report that is received data, date”).Activate With ActiveWorkbook.SlicerCaches ("Slicer Month") .SlicerItems ("1/09/2021").Selected = True .Slicertems ("1/07/2021").Selected = False .SlicerItems ("1/08/2021").Selected = False .SlicerItems ("1/10/2021").Selected = False (etc) End With With ActiveWorkbook. SlicerCaches ("Slicer_department") .Sliceritems ("Category1").Selected = True .Sliceritems(“Category2”). Selected = False. .Sliceritems(“Category2”). Selected = False. (etc) End with ActiveWorkbook.SlicerCaches ("Slicer_manager") .SlicerItems ("manager1").Selected = True .Slicertems ("manager2").Selected = False .SlicerItems ("manager3").Selected = False (etc) End with range(“F22:M22”).select selection.Copy Windows(“sheet where data is going”).Activate Range(“A1”).Select Activesheet.Paste
The above is repeated 3 times for the three separate lines to copy and paste.(A1, A2, A3). So the second copy paste takes the same but a different manager and the third copy paste takes the total of both the first and second manager.
Ideas also for robustness in programming would also be appreciated, as this has to be run by other employees.