VBA Code For Daily Updates

DSpink95

New Member
Joined
Feb 20, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I'm currently in the process of creating a system that will make viewing jobs for each day easier.
I currently have a central sheet with all information in for received work but I want to be able to have an option to click on a button with the current dates on and it shows only information from that date clicked. As the date on the button will change on a daily basis (linked with a formula to change based on todays date) what code could I use that will pull through the info for that date - data is in a worksheet called "All Jobs" cells B2:R and when the date is clicked I want it to open just that dates work - potentially in another tab "Daily View" any help would be appreciated!

1582209856565.png
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Code below assumes that the boxes are all shapes (like rectangles)
- assign the macro below to all the shapes
- ensure that how dates appear in the "box" is identical to how they look in the sheet
- the code filters data in the original sheet
- can modify to dump to another sheet if required

Using Application caller it avoids having to identify the object by name, but it does not work with everything
- modify the code so that the correct object text is read into variable boxText

VBA Code:
Sub FilterDates()
    Dim boxText As String
    boxText = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
    
    With Sheets("All Jobs").Range("A1")
        On Error Resume Next
        .Parent.ShowAllData
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=boxText
        .Parent.Activate
    End With
End Sub
 

DSpink95

New Member
Joined
Feb 20, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Yongle - thank you for your help! Looks like it has done the trick
 

Watch MrExcel Video

Forum statistics

Threads
1,122,354
Messages
5,595,674
Members
414,008
Latest member
zcaa0g

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top