Need a button function to share a workbook with colleague

[HMC62015]

Hi - thank you for reading !

I'm coming from Access VBA with excel function know -how....... this is what I'm trying to do in Excel from an Office 365 Apps for Enterprise suite.



I upload current sales data every week to "Data" sheet that is 38 columns and between 100-250 rows. The goal is to highlight the top 5 Sales agents for viewing, and limit the columns for management quick view.



I have a successful first attempt and I was able to create buttons with the names of Sales Rep(ColumnE) in that “Data” sheet to copy the rows and present in a “Report” Sheet.

From “Report”, you can click on See Summary and move to “Summary” sheet, then click Return to Data to go back.

Private Sub CommandButton9_Click()
Dim RC1125 As String
Dim x As Long
x = 2
Dim EROW As Long

Sheets("Data").Select
RC1125 = "Mary Smith"
Do While Cells(x, 1) <> ""
If Cells(x, 5) = RC1125 Then
Worksheets("Data").Rows(x).Copy
Worksheets("Report").Activate
EROW = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Report").Rows(EROW)
End If

Worksheets("Data").Activate
x = x + 1
Loop
Sheets("Report").Select
Application.CutCopyMode = False
End Sub

This is not able to capture which Sales Agent needs review, and how the Sales Agents compare to each other,

So I created another Sheet, “Office” that contains a constant list of Agents.

I am using COUNTIFS, SUMIFS, IFS in "Office" to fill in sales totals from the "Data", and using Top 3 conditional formatting.

This is a better prompt to management what they want to review.



Can you help re-write my button VBA in the “Data” sheet to button VBA in “Office”?

Here’s the loop I am trying to follow with new button:

1. filter the "Data" to the criteria in that row (Company and Sales Rep Name)
2. The filtered "Data" rows will copy into a "Report" sheet with only columns 3,4,5,6,8,9,13,16,27,28
3. Activate the temporary copied "Report" sheet for viewing
4. A finish button in Report that will unfilter the "Data" sheet, clearcontents in "Report" and return/activate back to "Office" where we can select another button

If I can do this, I will no longer need the “Summary” loop.

{The “Customers” sheet is a constant data dump that I use to calculate invoice due date with Terms and doesn’t appear in any VBA.}