none
Change monthly data in all sheets RRS feed

  • Question

  • Good morning,

    a) I have a worksheet that has many sheets that are linked to several MS Access databases. Some of these sheets represent cash expenses made by différents point of sale. All sheets are formatted exactly the same way, I have a field named "DateOfExpense" and I have to go sheet by sheet to show for example the expenses of October only (Last month data with a chronologic filter), is there a way that I can tell for all sheets to show last month data?

    I have a summary sheet that I would like to do that, in a cell if the administrator type October 2016, than all sheets are updated automatically to show that month data, is it possible?

    Thank you all for your time

    Claude from Quebec, Canada


    Claude Larocque

    Wednesday, November 9, 2016 9:28 AM

Answers

  • Let's say the date is entered in D1 on the Summary sheet. This cell should be formatted as a date, e.g. with the custom format mmmm yyyy.

    Right-click the sheet tab of the Summary sheet and select View Code from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim strDate As String
        Dim wsh As Worksheet
        If Not Intersect(Me.Range("D1"), Target) Is Nothing Then
            If Me.Range("D1").Value = "" Then
                For Each wsh In Worksheets
                    If wsh.Name <> Me.Name Then
                        wsh.UsedRange.AutoFilter Field:=1
                    End If
                Next wsh
            Else
                strDate = Format(Me.Range("D1").Value, "mm/dd/yyyy")
                For Each wsh In Worksheets
                    If wsh.Name <> Me.Name Then
                        wsh.UsedRange.AutoFilter Field:=1, _
                        Operator:=xlFilterValues, _
                        Criteria2:=Array(1, strDate)
                    End If
                Next wsh
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 9, 2016 4:16 PM

All replies

  • Let's say the date is entered in D1 on the Summary sheet. This cell should be formatted as a date, e.g. with the custom format mmmm yyyy.

    Right-click the sheet tab of the Summary sheet and select View Code from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim strDate As String
        Dim wsh As Worksheet
        If Not Intersect(Me.Range("D1"), Target) Is Nothing Then
            If Me.Range("D1").Value = "" Then
                For Each wsh In Worksheets
                    If wsh.Name <> Me.Name Then
                        wsh.UsedRange.AutoFilter Field:=1
                    End If
                Next wsh
            Else
                strDate = Format(Me.Range("D1").Value, "mm/dd/yyyy")
                For Each wsh In Worksheets
                    If wsh.Name <> Me.Name Then
                        wsh.UsedRange.AutoFilter Field:=1, _
                        Operator:=xlFilterValues, _
                        Criteria2:=Array(1, strDate)
                    End If
                Next wsh
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 9, 2016 4:16 PM
  • Sorry Hans for the delay, my yearly vacation :)

    Here is the result of your proposal:

    In the worksheet named "SOMMAIRE" and in the cell C28 I have created a personalized format mmmm aaaa (in Quebec french the yyyy is aaaa, I applied that format to the cell C28 and I have also apply the same format to all date fields in the all worksheets I want to filter. Then I have paste the code you send me in the "Sommaire" tab and I customize it as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim strDate As String
        Dim wsh As Worksheet
        If Not Intersect(Me.Range("C28"), Target) Is Nothing Then
            If Me.Range("C28").Value = "" Then
                For Each wsh In Worksheets
                    If wsh.Name <> Me.Name Then
                        wsh.UsedRange.AutoFilter Field:=1
                    End If
                Next wsh
            Else
                strDate = Format(Me.Range("C28").Value, "mmmm aaaa")
                For Each wsh In Worksheets
                    If wsh.Name <> Me.Name Then
                        wsh.UsedRange.AutoFilter Field:=1, _
                        Operator:=xlFilterValues, _
                        Criteria2:=Array(1, strDate)
                    End If
                Next wsh
            End If
        End If
    End Sub
    

    I received a 1004 execution error on this section:

    Here are some images that can help:

    thanks for your help


    Claude Larocque

    Thursday, November 17, 2016 9:28 PM
  • 1) Please use

                strDate = Format(Me.Range("C28").Value, "mm/dd/yyyy")

    irrespective of your local settings.

    2) Since you didn't specify which column contains the dates, I had to make up something. The code that I posted filters on column 1, just as an example.

    From your screenshot it's clear that 'Date de commande' is not column 1, but another column. Please change the two occurrences of Field:=1 accordingly.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 17, 2016 9:55 PM