VBA Macro for Selecting Current Week Range

[koreyjames]

Hi All,

I currently have this beast of a code running in Excel 2013:

Sub OBTAIN_RAW_DATA_MACRO()
Dim NextCol As Long 

  'Copy data 
    Workbooks.Open Filename:= _
        "\\elan\Cognos_Export\cognos\BDC_Reporting\Central Reporting Hub Raw Data-en-au.xlsx"    
ActiveCell.Columns("A:N").EntireColumn.Select    
Selection.Copy    
Windows("Central Reporting Hub V2.xlsm").Activate    
Sheets("Raw Data").Visible = True    
Sheets("Raw Data").Select    
Columns("A:N").EntireColumn.Select    
ActiveSheet.Paste    
Sheets("Raw Data").Visible = False    
Sheets("Title Page").Visible = True    
Windows("Central Reporting Hub Raw Data-en-au.xlsx").Activate    
ActiveWorkbook.Saved = True    
Windows("Central Reporting Hub Raw Data-en-au.xlsx").Application.CutCopyMode = False    
ActiveWindow.Close

Dim pt As PivotTableDim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables

                    pt.RefreshTable   
    Next pt
    Next ws  

End Sub

However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.

Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?

 

[Jeffrey20]

There are some not needed steps in your code, you generally don't need to select things or unhide them.

Is "Central Reporting Hub V2.xlsm" the workbook where the code is stored?
If no, then my code need some changes.

Sub OBTAIN_RAW_DATA_MACRO()

Dim NextCol As Long

  'Copy data
    Workbooks.Open Filename:= _
        "\\elan\Cognos_Export\cognos\BDC_Reporting\Central Reporting Hub Raw Data-en-au.xlsx"

Workbooks("Central Reporting Hub Raw Data-en-au").ActiveSheet.Columns("A:N").EntireColumn.Copy
ThisWorkbook.Sheets("Raw Data").Range("A:N").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
ThisWorkbook.Sheets("Title Page").Visible = True
Workbooks("Central Reporting Hub Raw Data-en-au").Saved = True
Workbooks("Central Reporting Hub Raw Data-en-au").Close

Dim pt As PivotTable
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
        pt.RefreshTable
        Next pt
    Next ws

End Sub

Try it, it should work.

---------

Now, about your question: Yes there is.

This works for me, it filters the dates in the current week from monday to sunday, in the first field. I'm not sure if this is exactly what you wanted but I'm sure this will allow you to adapt it.

Sub Macro()

Dim ws As Worksheet
Dim pt As PivotTable
Dim x As Date
Dim y As Date
Dim x2 As String
Dim y2 As String

x = Date - Application.WorksheetFunction.Weekday(Date, 3)
y = x + 6

x2 = Day(x) & "/" & Month(x) & "/" & Year(x)
y2 = Day(y) & "/" & Month(y) & "/" & Year(y)

For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.PivotFields(1).PivotFilters.Add2 Type:=xlDateBetween, Value1:=x2, Value2:=y2
        pt.RefreshTable
    Next pt
Next ws

End Sub

If you have any questions or anything, just ask...

Psd: "Dim NextCol As Long", where are you using "NextCol"? In the part that you posted it isn't used, so if not needed delete it.

 

[koreyjames]

The purposes of hiding and unhiding is because Senior Management don't generally care to look at the Raw Data, they just want to see the graphical representations so by having the hide/unhide macros built in it minimises the tab clutter down the bottom and forces them to look at only what is applicable (graphs).

I had been modifying this macro for a while so glad you pointed out some of the useless things I had left in there and I definitely took on some of the other changes that you made, so thank you!

As for the Slicer, I'm not sure whether it was because of the way I had my data laid out in the pivots, or because it was organised to group by week range in the date fields, but I adapted it and it worked like a charm, so thanks are are due to you again!

 

[Jeffrey20]

What I meant with the unhiding is that you don't need to unhide to "Raw data" in order to paste the new values, you could just paste them.

Glad some of my ideas where useful.

 

[koreyjames]

Oh cool I didn't know that, thanks mate!