Data Query based on Button Clicks VBA help required

[ajayxlnc2]

Duplicate Thread

http://www.ozgrid.com/forum/showthread.php?t=194969&p=747208#post747208
Mod Edit: Answer given on Ozgrid thread, closing this thread


Hi,

I have a planning dashboard which will provide the various aspects regarding the employee.

There are 3 sheets

Decision support tool
9-Box distribution
Emp Database

In Emp Database WS is where all the Employee Database is stored. B35 to AB35. The same range for Decision support tool WS and 9-Box distribution WS.

In Decision support tool WS I have created buttons for various Teams (Group2, Group3, Group4...)


My Requirement is,

Requirement 1

In Decision support tool WS, when I select the Tower from the drop down F23 and its related team in F24 and click on Run (button), I want to display the content in Columns B to O for the select tower and its related group. (The data should be picked from the table in Emp Database WS).

In Decision support tool, For that selected Tower and for selected Group, when I click on the Buttons (Group2, Group3, Group4, Group5, Group6) based on the employee group the content should be displayed.

For Example I have selected Tower1 in F23 and Tower_1_Team_2 in F24 and when I click on the any of the button (Group2, Group3, Group4, Group5, Group6), I want to display the content for that particular group which I have clicked.


Requirement 2

In Decision support tool, Columns Q to V and X to AB are updated manually by the managers.

Managers will update the information from Column O to Column AB (****Excluding the Column P, W, X --> as these columns contain formulas and they should not chage). Once they have updated the information and click on Update Date button i want the code to paste the values of that particular resource (Tower/Team selected in drop down) Paste Special --> Values From Column O to Column AB in Decision suppor tool WS to Employee Database

9-Box distribution WS

Based on the drop down for Tower/Team selected in F4 & F5 the data for that particular tower/group should be copied from Employee Database WS (Column B to Column AB) and paste special values in the table (Column B to Column AB).


Please help me out in this.

Your Help in highly appreciated.

Thanks in Advance.

Sample attached.

Code which I am currently using

Sub Clear_Data()
  Dim ws1          As Worksheet
  Dim LR1          As Long
  Dim LC1          As Long

  Set ws1 = Sheets("Decision support tool")

  With ws1
      LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
      LC1 = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious).Column
      If LR1 = 35 Then LR1 = 36
      .Range(.Cells(36, "B"), .Cells(LR1, LC1)).ClearContents
  End With
End Sub


Sub Run_Me()
  Dim ws          As Worksheet
  Dim ws1          As Worksheet
  Dim rng          As Range
  Dim LR          As Long
  Dim LC          As Long
  Dim LR1          As Long
  Dim LC1          As Long
  Dim x            As Long

  Set ws = Sheets("Emp Database")
  Set ws1 = Sheets("Decision support tool")

  With ws1
      LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
      LC1 = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious).Column
      If LR1 = 35 Then LR1 = 36
      .Range(.Cells(36, "B"), .Cells(LR1, LC1)).ClearContents
  End With

  With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row
      LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious).Column
      .Range(.Cells(35, "B"), .Cells(LR, LC)).AutoFilter field:=4, Criteria1:=ws1.Range("F23").Value
      .Range(.Cells(35, "B"), .Cells(LR, LC)).AutoFilter field:=5, Criteria1:=ws1.Range("F24").Value
      Set rng = .AutoFilter.Range
      x = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
      If x >= 1 Then
        .Range(.Cells(36, "B"), .Cells(LR, LC)).SpecialCells(xlCellTypeVisible).Copy
        ws1.Range("B36").PasteSpecial
        Application.CutCopyMode = False
      Else
        MsgBox "No Records Found"
      End If
      .AutoFilterMode = False
  End With
End Sub


Sub DoTheWork()
  Dim myBtn        As Button
  Dim myGroup      As String
  Dim ws          As Worksheet
  Dim LC          As Long
  Dim LR          As Long

  Set myBtn = Sheets("Decision support tool").Buttons(Application.Caller)
  myGroup = Right(myBtn.Caption, 1)
  Set ws = Sheets("Decision support tool")

  Application.ScreenUpdating = False
  With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row
      LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious).Column
      If Not .AutoFilterMode Then
        .Range(.Cells(35, "B"), .Cells(LR, LC)).AutoFilter
      End If
      .Range(.Cells(35, "B"), .Cells(LR, LC)).AutoFilter field:=8, Criteria1:=myGroup
  End With
  Application.ScreenUpdating = True
End Sub


Sub Show_All()
  On Error Resume Next
  Sheets("Decision support tool").ShowAllData
  On Error GoTo 0
End Sub


Sub Update()
  Dim ws          As Worksheet
  Dim ws1          As Worksheet
  Dim rng          As Range
  Dim rng1        As Range
  Dim cel          As Range
  Dim c            As Range
  Dim LR          As Long
  Dim LR1          As Long
  Dim LC1          As Long
  Set ws = Sheets("Emp Database")
  Set ws1 = Sheets("Decision support tool")

  With ws1
      LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
      LC1 = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious).Column
      Set rng1 = ws1.Range(ws1.Cells(36, "B"), ws1.Cells(LR1, "B")).SpecialCells(xlCellTypeVisible)
  End With

  With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row
      Set rng = ws.Range(ws.Cells(36, "B"), ws.Cells(LR, "B"))
  End With

  Application.ScreenUpdating = False
  For Each cel In rng1
      Set c = rng.Find(cel.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
      ws1.Range(ws1.Cells(cel.Row, "B"), ws1.Cells(cel.Row, LC1)).Copy
      ws.Cells(c.Row, "B").PasteSpecial (xlPasteValues)
  Next cel
  Application.ScreenUpdating = True
End Sub