Combine Two Sheets

I need to combine the Sheets 'Team Dashboard Wip' and 'New Layouts'..the issue is that they both have controls and some code. I get errors when I try. Using two sheets when it could be simplified would be preferable, right?
Here is what I would like:
Move the 'New Layouts' content into 'Team Dashboard WIP' and use the control at the top of 'Team Dashboard WIP' to select the person and the appropriate data.
dashboard-wipv2.xlsm
singleton2787Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Like this? Sample File Attached.

Sid

Code Used

Dim acell As Range

Private Sub Worksheet_change(ByVal Target As Range)
    If Not Intersect(Target, Range("E5")) Is Nothing Then
        Set acell = Sheets("Graph Data Entry").Columns(1).Find(What:=Range("E5").Value, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    
        If Not acell Is Nothing Then
            Range("E9").Value = acell.Offset(2, 2).Value
            Range("E10").Value = acell.Offset(4, 2).Value
            Range("E11").Value = acell.Offset(6, 2).Value
            Range("E12").Value = acell.Offset(7, 2).Value
            Range("F9").Value = acell.Offset(2, 3).Value
            Range("F10").Value = acell.Offset(4, 3).Value
            Range("F11").Value = acell.Offset(6, 3).Value
            Range("F12").Value = acell.Offset(7, 3).Value
            Range("G9").Value = acell.Offset(2, 4).Value
            Range("G10").Value = acell.Offset(4, 4).Value
            Range("G11").Value = acell.Offset(6, 4).Value
            Range("G12").Value = acell.Offset(7, 4).Value
            Range("H9").Value = acell.Offset(2, 5).Value
            Range("H10").Value = acell.Offset(4, 5).Value
            Range("H11").Value = acell.Offset(6, 5).Value
            Range("H12").Value = acell.Offset(7, 5).Value
         
        End If
        Range("E21:E29").ClearContents
        For I = 1 To 100
            If InStr(1, Sheets("Data").Cells(1, I), Range("E5").Value, vbTextCompare) Then
                For J = 21 To 29
                    ActiveSheet.Cells(J, 5).Value = Sheets("Data").Cells(J - 19, I)
                Next J
                Exit For
            End If
        Next
    End If
End Sub

Open in new window

Dashboard-wip.xlsm
0
 
SiddharthRoutCommented:
Like this? Sample File Attached.

Sid

Code Used

Private Sub Worksheet_change(ByVal Target As Range)
    If Not Intersect(Target, Range("E5")) Is Nothing Then
        Dim acell As Range
        
        Set acell = Sheets("Graph Data Entry").Columns(1).Find(What:=Range("E5").Value, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    
        If Not acell Is Nothing Then
            Range("E9").Value = acell.Offset(2, 2).Value
            Range("E10").Value = acell.Offset(4, 2).Value
            Range("E11").Value = acell.Offset(6, 2).Value
            Range("E12").Value = acell.Offset(7, 2).Value
            Range("F9").Value = acell.Offset(2, 3).Value
            Range("F10").Value = acell.Offset(4, 3).Value
            Range("F11").Value = acell.Offset(6, 3).Value
            Range("F12").Value = acell.Offset(7, 3).Value
            Range("G9").Value = acell.Offset(2, 4).Value
            Range("G10").Value = acell.Offset(4, 4).Value
            Range("G11").Value = acell.Offset(6, 4).Value
            Range("G12").Value = acell.Offset(7, 4).Value
            Range("H9").Value = acell.Offset(2, 5).Value
            Range("H10").Value = acell.Offset(4, 5).Value
            Range("H11").Value = acell.Offset(6, 5).Value
            Range("H12").Value = acell.Offset(7, 5).Value
         
        End If
    ElseIf Not Intersect(Target, Range("E18")) Is Nothing Then
        Range("E21:E29").ClearContents
        For I = 1 To 100
            If Sheets("Data").Cells(1, I) = Range("E18").Value Then
                For J = 21 To 29
                    ActiveSheet.Cells(J, 5).Value = Sheets("Data").Cells(J - 19, I)
                Next J
                Exit For
            End If
        Next
    End If
End Sub

Open in new window

Dashboard-wip.xlsm
0
 
singleton2787Author Commented:
Almost!!
If we can just remove the control at E18 (the lower one) and just make E5 the control that displays all the data and graph, that would be optimal, thanks!!!
0
 
singleton2787Author Commented:
You are the man!! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.