Improve Macro - Copy paste from spreadsheet with changing name.

[andrewcz]

Hi all,
I receive a sheet with data and i have recorded a macro to copy paste data to a new workbook and then that will create graphs.
However every time I receive the sheet the name changes ie. datasetsheet FY XXXX date 1, datasetsheet FY xxxx date 2 etc
I am trying to think of a way to write the macro which automatically updates the name of the new sheet as I want to attach the macro to
a button in excel. So the destination datasheet does not change but the new data does change.

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 below is what i currently have -

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)



I am trying to think of way to make a robust macro that i can attach to a button. I suppose the workflow could be to save the new sheet in a folder and then open the destination sheet and run the macro.
how would i write it with the changeling date. the date changes on a weekly basis.

Essentially we are just copying pasting three lines, with each line requiring a change from a pivot table (we have buttons that select manager, department etc).

So in summary -

- macro needs to be robust.
- date of data sheet changes
- macro needs to be assigned to a button.
- we can save the Datasheet into the folder, but then we would need to place the datapath in the macro.
- macro needs to copy paste (the 3 lines) into the same sheet all the time which is the destination sheet, ie write over the original data.

Kind regards and thanks,
Andrew

 

[andrewcz]

The below is what i could find from stackoverflow however i am not sure how to use it for the above macro.



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

 

[andrewcz]

The below is what i could find from stackoverflow but i am not sure how to put it into a macro

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