VBA Daily Sales Placement

[BijanBorazjani]

I am trying to build a VBA code that can automatically place the previous days sales into the corresponding employees sheet based on the daily upload. The report will have the employee name in the daily sales as well as their own corresponding sheet. I would like it to be a click of a button that looks up the day of the report and places the correct data in the corresponding employees sheet.

Book1
	A	B	C	D
1		Call Sales	In Store Sales	Total Sales
2	Employee Name
3	Employee 1	1	33	34
4	Emplyoee 2	2	33	35
5	Employee 3	3	33	36
6	Total Sales	6	99	105
7
8
9
10	Report Date	8/2/2022
Daily Sales

Cell Formulas
Range	Formula
D3:D5	D3	=SUM(B3:C3)
B6:D6	B6	=SUM(B3:B5)
B10	B10	=TODAY()-1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Week 1	Tickets	Calls	Survey Score	Week 2	Tickets	Calls	Survey Score	Week 3	Tickets	Calls	Survey Score	Week 4	Tickets	Calls	Survey Score
2	1/3/2022				1/10/2022				1/17/2022				1/24/2022
3	1/4/2022				1/11/2022				1/18/2022				1/25/2022
4	1/5/2022				1/12/2022				1/19/2022				1/26/2022
5	1/6/2022				1/13/2022				1/20/2022				1/27/2022
6	1/7/2022				1/14/2022				1/21/2022				1/28/2022
7
8	Week 5	Tickets	Calls	Survey Score	Week 6	Tickets	Calls	Survey Score	Week 7	Tickets	Calls	Survey Score	Week 8	Tickets	Calls	Survey Score
9	1/31/2022				2/7/2022				2/14/2022				2/21/2022
10	2/1/2022				2/8/2022				2/15/2022				2/22/2022
11	2/2/2022				2/9/2022				2/16/2022				2/23/2022
12	2/3/2022				2/10/2022				2/17/2022				2/24/2022
13	2/4/2022				2/11/2022				2/18/2022				2/25/2022
14
15	Week 9				Week 10				Week 11				Week 12	Tickets	Calls	Survey Scores
16	2/28/2022				3/7/2022				3/14/2022				3/21/2022
17	3/1/2022				3/8/2022				3/15/2022				3/22/2022
18	3/2/2022				3/9/2022				3/16/2022				3/23/2022
19	3/3/2022				3/10/2022				3/17/2022				3/24/2022
20	3/4/2022				3/11/2022				3/18/2022				3/25/2022
21
22	Week 13	Tickets	Calls	Survey Score	Week 14	Tickets	Calls	Survey Score	Week 15	Tickets	Calls	Survey Score	Week 16	Tickets	Calls	Survey Score
23	3/28/2022				4/4/2022				4/11/2022				4/18/2022
24	3/29/2022				4/5/2022				4/12/2022				4/19/2022
25	3/30/2022				4/6/2022				4/13/2022				4/20/2022
26	3/31/2022				4/7/2022				4/14/2022				4/21/2022
27	4/1/2022				4/8/2022				4/15/2022				4/22/2022
28
29	Week 17	Tickets	Calls	Survey Score	Week 18	Tickets	Calls	Survey Score	Week 19	Tickets	Calls	Survey Score	Week 20	Tickets	Calls	Survey Score
30	4/25/2022				5/2/2022				5/9/2022				5/16/2022
31	4/26/2022				5/3/2022				5/10/2022				5/17/2022
32	4/27/2022				5/4/2022				5/11/2022				5/18/2022
33	4/28/2022				5/5/2022				5/12/2022				5/19/2022
34	4/29/2022				5/6/2022				5/13/2022				5/20/2022
35
36	Week 21	Tickets	Calls	Survey Score	Week 22	Tickets	Calls	Survey Score	Week 23	Tickets	Calls	Survey Score	Week 24	Tickets	Calls	Survey Score
37	5/23/2022				5/30/2022				6/6/2022				6/13/2022
38	5/24/2022				5/31/2022				6/7/2022				6/14/2022
39	5/25/2022				6/1/2022				6/8/2022				6/15/2022
40	5/26/2022				6/2/2022				6/9/2022				6/16/2022
41	5/27/2022				6/3/2022				6/10/2022				6/17/2022
42
43	Week 25	Tickets	Calls	Survey Score	Week 26	Tickets	Calls	Survey Score	Week 27	Tickets	Calls	Survey Score	Week 28	Tickets	Calls	Survey Score
44	6/20/2022				6/27/2022				7/4/2022				7/11/2022
45	6/21/2022				6/28/2022				7/5/2022				7/12/2022
46	6/22/2022				6/29/2022				7/6/2022				7/13/2022
47	6/23/2022				6/30/2022				7/7/2022				7/14/2022
48	6/24/2022				7/1/2022				7/8/2022				7/15/2022
49
50	Week 29	Tickets	Calls	Survey Score	Week 30	Tickets	Calls	Survey Score	Week 31	Tickets	Calls	Survey Score	Week 32	Tickets	Calls	Survey Score
51	7/18/2022				7/25/2022				8/1/2022				8/8/2022
52	7/19/2022				7/26/2022				8/2/2022				8/9/2022
53	7/20/2022				7/27/2022				8/3/2022				8/10/2022
54	7/21/2022				7/28/2022				8/4/2022				8/11/2022
55	7/22/2022				7/29/2022				8/5/2022				8/12/2022
56
57	Week 33	Tickets	Calls	Survey Score	Week 34	Tickets	Calls	Survey Score	Week 35	Tickets	Calls	Survey Score	Week 36	Tickets	Calls	Survey Score
58	8/15/2022				8/22/2022				8/29/2022				9/5/2022
59	8/16/2022				8/23/2022				8/30/2022				9/6/2022
60	8/17/2022				8/24/2022				8/31/2022				9/7/2022
61	8/18/2022				8/25/2022				9/1/2022				9/8/2022
62	8/19/2022				8/26/2022				9/2/2022				9/9/2022
63
64	Week 37	Tickets	Calls	Survey Score	Week 38	Tickets	Calls	Survey Score	Week 39	Tickets	Calls	Survey Score	Week 40	Tickets	Calls	Survey Score
65	9/12/2022				9/19/2022				9/26/2022				10/3/2022
66	9/13/2022				9/20/2022				9/27/2022				10/4/2022
67	9/14/2022				9/21/2022				9/28/2022				10/5/2022
68	9/15/2022				9/22/2022				9/29/2022				10/6/2022
69	9/16/2022				9/23/2022				9/30/2022				10/7/2022
70
71	Week 41	Tickets	Calls	Survey Score	Week 42	Tickets	Calls	Survey Score	Week 43	Tickets	Calls	Survey Score	Week 44	Tickets	Calls	Survey Score
72	10/10/2022				10/17/2022				10/24/2022				10/31/2022
73	10/11/2022				10/18/2022				10/25/2022				11/1/2022
74	10/12/2022				10/19/2022				10/26/2022				11/2/2022
75	10/13/2022				10/20/2022				10/27/2022				11/3/2022
76	10/14/2022				10/21/2022				10/28/2022				11/4/2022
77
78	Week 45	Tickets	Calls	Survey Score	Week 46	Tickets	Calls	Survey Score	Week 47	Tickets	Calls	Survey Score	Week 48	Tickets	Calls	Survey Score
79	11/7/2022				11/14/2022				11/21/2022				11/28/2022
80	11/8/2022				11/15/2022				11/22/2022				11/29/2022
81	11/9/2022				11/16/2022				11/23/2022				11/30/2022
82	11/10/2022				11/17/2022				11/24/2022				12/1/2022
83	11/11/2022				11/18/2022				11/25/2022				12/2/2022
84
85	Week 49	Tickets	Calls	Survey Score	Week 50	Tickets	Calls	Survey Score	Week 51	Tickets	Calls	Survey Score	Week 52	Tickets	Calls	Survey Score
86	12/5/2022				12/12/2022				12/19/2022				12/26/2022
87	12/6/2022				12/13/2022				12/20/2022				12/27/2022
88	12/7/2022				12/14/2022				12/21/2022				12/28/2022
89	12/8/2022				12/15/2022				12/22/2022				12/29/2022
90	12/9/2022				12/16/2022				12/23/2022				12/30/2022
91
Employee 1

 

[Colo]

Hi,
There are "Call Sales" and "In Store Sales" in the Daily Sales sheet, but only Calls can be seen in the corresponding employees' sheet. So the following sample code would place only "Call Sales" data from the Daily Sales sheet to "Call" cell in each worksheet.
Assuming worksheet names for Employees are based on range A3 to A5 in the Daily Sales sheet. (*Found a typo in A5)

Sub Sample1()
    Dim c As Range, rRptDate As Range, i As Long
    Dim shDaily As Worksheet, shEmp As Worksheet

    Set shDaily = ThisWorkbook.Sheets("Daily Sales")

    For i = 3 To 5    'Looping through employees sheet
        Set shEmp = ThisWorkbook.Sheets(shDaily.Cells(i, "A").Value)    'employee sheet
        Set c = shEmp.Range("A1:A" & shEmp.Cells(Rows.Count, 1).End(xlUp).Row)    'Date range
        Set c = Union(c, c.Offset(, 4), c.Offset(, 8), c.Offset(, 12))
        Set rRptDate = c.Find(shDaily.Range("B10").Value)
        If Not rRptDate Is Nothing Then
            rRptDate.Offset(, 2).Value = shDaily.Cells(i, "B").Value    'Calls
            'I have no idea where to put "In Store Sales"
        End If
    Next
End Sub

 

[BijanBorazjani]

Hi,
There are "Call Sales" and "In Store Sales" in the Daily Sales sheet, but only Calls can be seen in the corresponding employees' sheet. So the following sample code would place only "Call Sales" data from the Daily Sales sheet to "Call" cell in each worksheet.
Assuming worksheet names for Employees are based on range A3 to A5 in the Daily Sales sheet. (*Found a typo in A5)

Sub Sample1()
    Dim c As Range, rRptDate As Range, i As Long
    Dim shDaily As Worksheet, shEmp As Worksheet

    Set shDaily = ThisWorkbook.Sheets("Daily Sales")

    For i = 3 To 5    'Looping through employees sheet
        Set shEmp = ThisWorkbook.Sheets(shDaily.Cells(i, "A").Value)    'employee sheet
        Set c = shEmp.Range("A1:A" & shEmp.Cells(Rows.Count, 1).End(xlUp).Row)    'Date range
        Set c = Union(c, c.Offset(, 4), c.Offset(, 8), c.Offset(, 12))
        Set rRptDate = c.Find(shDaily.Range("B10").Value)
        If Not rRptDate Is Nothing Then
            rRptDate.Offset(, 2).Value = shDaily.Cells(i, "B").Value    'Calls
            'I have no idea where to put "In Store Sales"
        End If
    Next
End Sub

This is perfect! Thank you so much. If you could, can you break down what everything does so that I can put it in my memory for later?

 

[Colo]

Glad to hear it works. I hope that some adding comments would help your understanding of what this code does.

Sub Sample1()
'Define Variables(Variable is a storage location within the computer's memory)
'Explicitly declaring variables and data types may result in more efficient and faster macros.
    Dim c As Range    'Define "Date" ranges in each employee worksheet
    Dim rRptDate As Range    'A found date cell as a result of the Find method
    Dim i As Long    'A counter for looping
    Dim shDaily As Worksheet    'A Variable to improve readability.
    Dim shEmp As Worksheet  'A Variable to improve readability.

'This variable can be used the same as a worksheet after the following line.
    Set shDaily = ThisWorkbook.Sheets("Daily Sales")


    'Looping through employees sheet.
    'There is information for employees' names in A3 to A5 in the "Daily Sales" sheet,
    '    so using those to define each corresponding employee sheet.
    For i = 3 To 5
        'define employee sheet
        Set shEmp = ThisWorkbook.Sheets(shDaily.Cells(i, "A").Value)
        'Specify the search destination range for Date in the corresponding employee sheet
        Set c = shEmp.Range("A1:A" & shEmp.Cells(Rows.Count, 1).End(xlUp).Row)
        Set c = Union(c, c.Offset(, 4), c.Offset(, 8), c.Offset(, 12))
        'Finding the Date cell for putting data from Daily Sales" sheet
        Set rRptDate = c.Find(shDaily.Range("B10").Value)
       
        If Not rRptDate Is Nothing Then 'If the Date is found...
            rRptDate.Offset(, 2).Value = shDaily.Cells(i, "B").Value    'Calls
            'I have no idea where to put "In Store Sales"
        End If
    Next
End Sub