VB Setting one drop down = to another on a different sheet in excel

[InSanity]

Hey Guys hopefully someone can help with this

i have a Excel document that has multiple sheets on it and each sheet has a drop down thats uses the same value now what i want to do is when i select the one it changes the values in the to the same thing rest but those ones must still be independant so i cant just point them to the same index.

so eg Sheet1.drop down you select a number of 5 then sheet 2 .drop down must also equal 5

this is more or less the code i can get any help :


Sub TracerCode()
'
' TracerCode Macro
' Changes the tracercode
'
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Status").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Status").CurrentPage = _
Sheets("Principal Performance").Range("G2").Value
ActiveSheet.Select
Sheets("Principal Performance").Select

'
' Main Macro
'Changes the actual pivots accordingly for the principal level

'
' Sheets("Sheet1").Select
' ActiveSheet.PivotTables("PivotTable4").PivotFields("TracerStatus").ClearAllFilters
' ActiveSheet.PivotTables("PivotTable4").PivotFields("TracerStatus").CurrentPage = _
'Sheets("Principal Performance").Range("G2").Value
' 'ActiveSheet.Select
'Sheets("Sheet1").Select
'ActiveSheet.PivotTables("PivotTable4").PivotFields("PrincipalDesc").ClearAllFilters
'ActiveSheet.PivotTables("PivotTable4").PivotFields("PrincipalDesc").CurrentPage = _
' Sheets("Principal Performance").Range("G4").Value
' Sheets("Principal Performance").Select

Dim Pperc As Worksheet
Dim Pval As Worksheet
Dim Teststring1 As String
Dim Teststring2 As String

Set Pperc = Worksheets("sheet1")
Set Pval = Worksheets("sheet2")


ActiveSheet.PivotTables("PivotTable1").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

ActiveSheet.PivotTables("PivotTable2").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

ActiveSheet.PivotTables("PivotTable3").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

ActiveSheet.PivotTables("PivotTable4").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

ActiveSheet.PivotTables("PivotTable5").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable5").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

ActiveSheet.PivotTables("PivotTable6").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

ActiveSheet.PivotTables("PivotTable7").PivotFields("PD").ClearAllFilters
ActiveSheet.PivotTables("PivotTable7").PivotFields("PD").CurrentPage = _
ActiveSheet.Range("G4").Value

Teststring1 = Pperc.OLEObjects("Drop Down 5").Object.Value
Teststring2 = Pval.OLEObjects("Drop Down 2").Object.Value

ActiveWindow.SmallScroll Down:=-6
End Sub

P.s the middel part is just what the drop down activates

 

[InSanity]

LOL 26 views and no reply

 

[FarligOpptreden]

I guess few people work with VB macros these days... They're, well, deprecated.

 

[InSanity]

yeah well i got it working and i have never studied vb or ever done macros before

 

[FarligOpptreden]

Well done

Post the answer so this doesn't happen to someone else.

Funny how a single post can sum up a forumite's complete online identity, huh?