Data Validation List, Multiple Sheets. Update one, update them all

[DragonzBreath]

Hi Guys,

I'm writing a budget spreadsheet. I have the same data validation list on multiple sheets in a workbook. I would like to be able to update the DVL on any one of the worksheets and have it update/mirror on all the other worksheets. I've googled for this and get nothing close to what I'm after. Perhaps my search terms are garbage, I don't know.

Is anyone able to help with the VB code to facilitate this?

Also, I have a pivot table which has the average number of days in a given time period. i.e. a month is 30.42 days (365/12), a quarter is 91.25 days (365/4)
I utilise VLOOKUP to work out costs of bills, by working them back to a daily cost and then multiplying them by the number of days in a give period based on the DVL in the above question.
Is there a more simple way to do this? i.e. does excel have a built in function for working out the average number of days in a month/quarter that doesn't require referencing a pivot table?

Thank you for reading, and thank you in advance for any input

DB

 

[DanteAmor]

Good Afternoon,

I have a similar issue to dragonzbreath; unfortunately myknowledge of VBA is very limited. I am very versed in excel, so I have noissues there.
I would like to select my “yes/no” validation list on sheet1, cell A1 and then in turn it will update all of my tabs to “yes” from afilter list in cell A1 (in all 270 of my sheets). This is to consolidate eachreport page (each tab is using the filter sort in cell A1). I would like tofigure this out, because otherwise I have to select 270 tabs and select “yes”. As far as I can tell, there isn’t an easy wayto do this in excel, unless you use some coding. If you have a better or moreefficient way of achieving this, I’m all ears!

Please advise, if you require further information

Put the following code in the events of Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet, myVal As String, wcuenta As String, cell As String
    
    cell = "A1"
    If Target.Count > 1 Then Exit Sub
    If Target.Address(False, False) <> cell Then Exit Sub
    If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        myVal = Target.Value
        Application.EnableEvents = False
        For Each ws In Sheets
            If ws.Name <> ActiveSheet.Name Then
                wcuenta = 0
                On Error Resume Next
                wcuenta = ws.Range(cell).SpecialCells(xlCellTypeSameValidation).Count
                On Error GoTo 0
                If wcuenta > 0 Then
                    'ws.Unprotect "abc"
                    ws.Range(cell).Value = myVal
                    'ws.Protect "abc"
                End If
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

SHEET EVENT
Right click the tab of Sheet1 , select view code and paste the code into the window that opens up.

Return to Sheet1, change the value in cell A1. The macro will update the value in A1 on all sheets.

 

[jadams121]

Thanks for looking into thisfor me Dante. I tried it and it doesn’t work. I don’t know where the issuelies, but it wont run. I have tried the code (below) and it works, except, I wantit applied to most of my sheets (270) and not to about 3 other sheets. Is therean easy way to slightly modify this code to make it work.

Subapply_autofilter_across_worksheets()
Dim p As Integer, q AsInteger
p = Worksheets.Count
For q = 1 To p
With Worksheets(q)
.Range("a1").AutoFilterfield:=1, Criteria1:="yes"
End With
Next q
End Sub

 

[DanteAmor]

Thanks for looking into thisfor me Dante. I tried it and it doesn’t work. I don’t know where the issuelies, but it wont run. I have tried the code (below) and it works, except, I wantit applied to most of my sheets (270) and not to about 3 other sheets. Is therean easy way to slightly modify this code to make it work.

I thought you had a validation list in cell A1 on all sheets. So it is not so similar to the content of this thread.

Try this

Sub apply_autofilter_across_worksheets()
    Dim q As Integer
    For q = 1 To Worksheets.Count
        With Worksheets(q)
            Select Case Worksheets(q).Name
                'Write here the names of the sheets where the filter should not be applied.
                Case [COLOR=#ff0000]"Sheet1", "Sheet2", "sheet3"[/COLOR]
                Case Else
                    .Range("a1").AutoFilter field:=1, Criteria1:="yes"
            End Select
        End With
    Next q
End Sub

 

[jadams121]

IT WORKS EXACTLY AS HOPED!!! Thanks for all of your helpDante! I really appreciate intelligent people, such as yourself helping peopleout with this stuff.

Just one other thing…is there an easy way to make a macrosimilar to what you built here, except to reverse it back and reset all of thefilters to show all?

 

[DanteAmor]

IT WORKS EXACTLY AS HOPED!!! Thanks for all of your helpDante! I really appreciate intelligent people, such as yourself helping peopleout with this stuff.

Just one other thing…is there an easy way to make a macrosimilar to what you built here, except to reverse it back and reset all of thefilters to show all?

Try this

Sub reset_autofilter_across_worksheets()
    Dim q As Integer
    On Error Resume Next
    For q = 1 To Worksheets.Count
        With Worksheets(q)
            Select Case Worksheets(q).Name
                'Write here the names of the sheets where the filter should not be applied.
                Case "Sheet1", "Sheet2", "sheet3"
                Case Else
                    .ShowAllData
            End Select
        End With
    Next q
End Sub