Changing file name and dates VBA macro

Theassassin11 · Nov 3rd 2021

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.

Code

    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

Display More

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.

**royUK** · Nov 3rd 2021

An example workbook might help.

Theassassin11 · Nov 4th 2021

Hi royUK I hope your well!

Thank you for your response. I cant post an example workbook due to privacy.

I am going to post what i found from stack overflow below.

The file name does come up as the previous monday so the code the below does need some improvement.

Code

Dim lastMonth As Date
Dim startLastMonth As Date
Dim startLastMonthSlicerTxt As String
Dim startLastMonthFileTxt As String
Dim fileName As String

' this would be the generic part of the file name without the date
fileName = "my file name "

' subtract a month from current date
lastMonth = DateAdd("m", -1, Date)
' set the date to the 1st of last month
startLastMonth = DateSerial(Year(lastMonth), Month(lastMonth), 1)
' convert the date to text so you can use in slicer name
startLastMonthSlicerTxt = Format(startLastMonth, "d/mm/yyyy")
' convert the date to text so you can use in file name
startLastMonthFileTxt = Format(startLastMonth, "d-mm-yyyy")

' this appends the date and file extension to your generic file name
fileName = fileName & startLastMonthTxt & ".xlsx"
' as per your code, we now activate the Window but this time using the dynamic file name
Windows(filename).Active

' loop through the slicer items and only select if value matches last month
With ActiveWorkbook.SlicerCaches("Slicer_Month")
    For Each si In .SlicerItems
        If si.Name = startLastMonthTxt Then
            si.Selected = True
        Else
            si.Selected = False
        End If
    Next si
End With

Display More

Changing file name and dates VBA macro

Participate now!

Share

Tags