Macro to pull and calculate daily data to weekly report

[diripio]

I create a weekly report that calculates past week's expenses. Usually, I would copy and paste the new data that I got from another workbook and create a weekly report based off of that. How can I create a macro to pull the daily numbers and calculate them weekly? How can I also have this macro file update every week if I were to refresh the data workbook?

 

[Zot]

Helpers here probably need to see the layout of of the file you copy from and how you laid up your weekly report. That way it is easier for them to know where to begin

 

[diripio]

Basically for the first table, I want to sum up first seven days and put the total into their respective regions. For instance, week 1 would be from 03/01 - 03/08. Week 2 would be from 03/09 - 03/16. Is it possible to have the table automatically fill in the data if I copy/pasted new data in another worksheet?

Region	Wk 1	Wk2	Wk3	Wk4	Wk5
Korea
Japan
China
India
Israel
Russia
Denmark
Ireland
England
Pacific Islands
East
West
North
South
Grand Total	$0	$0	$0	$0	$0

Region	3/1/2021	3/2/2021	3/3/2021	3/4/2021	3/5/2021	3/6/2021	3/7/2021	3/8/2021	3/9/2021	3/10/2021	3/11/2021	3/12/2021	3/13/2021	3/14/2021	3/15/2021	3/16/2021	3/17/2021	3/18/2021	3/19/2021	3/20/2021	3/21/2021	3/22/2021	3/23/2021	3/24/2021	3/25/2021	3/26/2021	3/27/2021	3/28/2021	3/29/2021	3/30/2021	3/31/2021	4/1/2021	4/2/2021	4/3/2021	4/4/2021	4/5/2021	4/6/2021	4/7/2021	4/8/2021	Grand Total
Korea	59,299	61,877	59,246	59,845	60,148	59,574	59,800	61,867	64,481	64,569	64,565	64,813	63,020	62,739	64,033	67,156	66,595	66,987	66,340	64,450	63,844	65,762	67,039	67,079	67,516	66,987	64,107	63,196	64,911	67,126	67,964	69,900	69,253	69,352	68,406	71,143	74,235	75,328	15,541	2,500,091
Japan	2,143	1,982	2,010	2,024	2,029	2,007	2,007	2,027	2,030	2,072	2,082	2,062	2,063	2,073	2,048	2,071	2,089	2,108	2,144	2,227	2,215	2,222	2,233	2,235	2,211	2,210	2,206	2,206	2,220	2,316	2,315	2,368	2,335	2,393	2,393	2,460	2,709	2,664	532	83,742
China	445,193	489,549	452,443	437,984	426,414	403,828	400,972	413,129	431,986	458,040	443,399	442,569	434,545	430,232	493,087	430,587	423,101	431,420	433,065	429,011	420,584	424,042	427,401	429,738	430,555	431,346	422,820	420,705	424,263	422,339	424,225	420,894	428,729	423,281	415,549	423,298	428,223	396,953	42,068	16,407,566
India	166,041	167,429	170,808	177,850	171,862	174,631	172,476	171,077	174,657	171,999	171,662	171,038	175,790	176,409	181,867	180,272	176,531	179,480	179,132	177,649	174,850	174,969	177,956	175,145	176,043	172,547	169,479	171,487	173,606	175,364	178,036	179,970	179,164	176,472	176,407	179,754	178,588	175,054	32,045	6,685,596
Israel	3,509	3,593	3,574	3,671	3,722	3,554	3,554	3,530	3,471	3,418	3,456	3,575	3,504	3,525	3,532	3,569	3,520	3,650	3,834	3,619	3,592	3,984	3,386	3,400	3,191	3,159	3,238	3,171	3,318	3,252	3,277	3,311	3,374	3,194	3,184	3,267	3,274	2,977	430	131,356
Russia	175,671	181,160	181,449	189,881	184,281	180,263	180,448	190,694	205,121	206,183	212,286	217,404	219,914	202,105	197,328	193,269	202,788	203,276	200,507	195,294	197,235	194,580	191,080	196,064	205,805	192,783	191,977	188,613	188,689	226,287	196,354	204,980	201,196	194,642	194,590	198,563	201,252	189,388	32,820	7,506,217
Denmark	60,927	60,957	62,634	62,306	63,389	63,305	61,176	62,118	61,415	61,460	60,996	61,911	61,593	61,227	60,737	60,081	62,181	63,364	64,549	64,364	61,592	61,901	64,595	64,312	65,870	67,335	65,225	63,991	63,624	62,256	62,009	63,150	63,144	63,146	62,751	63,223	64,689	64,452	11,067	2,399,022
Ireland	17,854	20,370	20,950	21,489	20,720	20,669	20,712	22,488	23,306	22,129	22,799	22,088	21,539	21,804	21,409	21,592	22,576	21,987	21,764	21,390	21,468	21,718	22,626	22,504	21,188	21,008	20,790	20,652	21,853	22,124	20,767	22,023	22,858	23,899	24,018	24,227	22,183	20,621	1,308	827,468
England	168,168	165,011	166,936	172,460	164,487	161,237	163,719	152,771	151,214	151,634	139,025	131,545	125,832	127,083	127,502	128,852	127,693	129,535	129,216	127,146	129,719	128,961	127,473	127,002	126,339	127,867	131,327	129,475	132,075	137,758	141,688	144,400	142,066	137,940	134,618	138,136	135,908	125,118	22,294	5,331,231
Pacific Islands	41,680	35,559	34,452	35,299	34,050	33,870	33,781	33,838	33,735	33,784	34,778	34,990	34,652	34,562	35,253	35,361	35,830	37,469	37,153	36,651	37,005	37,129	38,419	39,190	38,965	38,457	38,621	38,738	40,721	40,791	40,453	41,260	42,538	40,725	40,361	38,849	39,098	33,326	5,199	1,416,594
East	92,336	94,193	93,431	93,737	93,563	89,610	88,610	89,326	89,012	89,768	90,126	92,607	93,002	92,850	92,181	91,925	91,132	91,106	92,793	91,741	92,082	91,972	91,104	91,372	92,611	94,344	92,475	92,217	92,494	93,688	94,894	95,061	95,954	95,949	97,064	98,561	97,200	80,391	11,675	3,524,157
West	173,325	173,313	173,564	175,634	175,010	162,088	153,955	175,499	176,134	175,698	177,182	181,913	181,256	180,417	183,754	185,204	185,695	177,713	182,712	182,184	179,959	181,970	184,480	183,276	181,574	191,357	194,937	190,805	189,886	189,833	191,985	180,329	174,103	172,271	171,013	173,596	175,911	152,030	20,377	6,811,942
North	78,164	76,784	75,262	78,795	81,356	78,549	78,019	78,147	78,309	78,097	77,850	79,583	78,472	77,775	78,111	78,041	79,223	79,174	75,757	65,202	60,167	61,036	58,396	57,787	57,436	57,331	57,883	57,872	57,871	59,087	58,230	58,447	58,862	58,645	58,626	58,839	59,192	52,165	6,969	2,605,511
South	55,743	55,813	55,706	57,008	57,017	55,260	55,347	56,025	56,375	55,725	55,865	56,652	55,965	55,773	56,108	57,433	58,511	60,089	59,975	53,354	52,353	51,016	48,931	49,696	50,131	53,763	53,045	54,026	54,546	54,121	54,162	52,824	54,472	53,625	53,975	55,375	55,499	52,896	8,415	2,092,613
Grand Total	1,540,052	1,587,590	1,552,464	1,567,982	1,538,048	1,488,446	1,474,578	1,512,534	1,551,247	1,574,578	1,556,071	1,562,750	1,551,147	1,528,575	1,596,951	1,535,412	1,537,465	1,547,357	1,548,940	1,514,283	1,496,664	1,501,263	1,505,119	1,508,798	1,519,436	1,520,494	1,508,132	1,497,154	1,510,078	1,556,340	1,536,360	1,538,915	1,538,046	1,515,533	1,502,955	1,529,291	1,537,960	1,423,363	210,737	58,323,106

 

[Zot]

How do you calculate week, by block of weekdays such as if 1st day of month is on Friday the the 1st week is only 3 days or by number of 7 days regardless what day the 1st day is? This would give head up to those helpers out there.

 

[Zot]

My bad. You have mentioned that your week is by days 1 Mac ~ 8 Mac = Wk1? Perhaps 1/3~7/3 for 7 days?

 

[Zot]

I'm assuming the Wk1 is from Day1 to Day7. If not adjust accordingly. If your data is more than a month, it will keep adding the 1st week, 2nd week, etc accordingly like in your sample data.

Sub WeeklySummary()

Dim Wk As String
Dim rowSum As Range, colSum As Range
Dim rngSumRegion As Range, rngSumCol As Range
Dim rowData As Range, colData As Range
Dim rngDataRegion As Range, rngDataCol As Range
Dim wsSummary As Worksheet, wsData As Worksheet

Set wsSummary = ActiveWorkbook.Sheets("Sheet1")
Set wsData = ActiveWorkbook.Sheets("Sheet2")

Set rngSumRegion = wsSummary.Range("A2", wsSummary.Range("A1").End(xlDown).Offset(-1, 0))
Set rngSumCol = wsSummary.Range("B1", wsSummary.Cells(1, Columns.Count).End(xlToLeft))
Set rngDataRegion = wsData.Range("A2", wsData.Range("A1").End(xlDown).Offset(-1, 0))
Set rngDataCol = wsData.Range("B1", wsData.Cells(1, Columns.Count).End(xlToLeft).Offset(0, -1))

For Each colData In rngDataCol
    Set colSum = rngSumCol.Find("Wk" & GetWk(colData.Text))
    For Each rowData In rngDataRegion
        Set rowSum = rngSumRegion.Find(rowData)
        wsSummary.Cells(rowSum.Row, colSum.Column) = wsSummary.Cells(rowSum.Row, colSum.Column) + wsData.Cells(rowData.Row, colData.Column)
    Next
Next

End Sub

Function GetWk(dt As Date) As Long

Select Case Day(dt)
    Case 1 To 7
        GetWk = 1
    Case 8 To 14
        GetWk = 2
    Case 15 To 21
        GetWk = 3
    Case 22 To 28
        GetWk = 4
    Case Is > 28
        GetWk = 5
End Select

End Function

 

[diripio]

I'm assuming the Wk1 is from Day1 to Day7. If not adjust accordingly. If your data is more than a month, it will keep adding the 1st week, 2nd week, etc accordingly like in your sample data.

Sub WeeklySummary()

Dim Wk As String
Dim rowSum As Range, colSum As Range
Dim rngSumRegion As Range, rngSumCol As Range
Dim rowData As Range, colData As Range
Dim rngDataRegion As Range, rngDataCol As Range
Dim wsSummary As Worksheet, wsData As Worksheet

Set wsSummary = ActiveWorkbook.Sheets("Sheet1")
Set wsData = ActiveWorkbook.Sheets("Sheet2")

Set rngSumRegion = wsSummary.Range("A2", wsSummary.Range("A1").End(xlDown).Offset(-1, 0))
Set rngSumCol = wsSummary.Range("B1", wsSummary.Cells(1, Columns.Count).End(xlToLeft))
Set rngDataRegion = wsData.Range("A2", wsData.Range("A1").End(xlDown).Offset(-1, 0))
Set rngDataCol = wsData.Range("B1", wsData.Cells(1, Columns.Count).End(xlToLeft).Offset(0, -1))

For Each colData In rngDataCol
    Set colSum = rngSumCol.Find("Wk" & GetWk(colData.Text))
    For Each rowData In rngDataRegion
        Set rowSum = rngSumRegion.Find(rowData)
        wsSummary.Cells(rowSum.Row, colSum.Column) = wsSummary.Cells(rowSum.Row, colSum.Column) + wsData.Cells(rowData.Row, colData.Column)
    Next
Next

End Sub

Function GetWk(dt As Date) As Long

Select Case Day(dt)
    Case 1 To 7
        GetWk = 1
    Case 8 To 14
        GetWk = 2
    Case 15 To 21
        GetWk = 3
    Case 22 To 28
        GetWk = 4
    Case Is > 28
        GetWk = 5
End Select

End Function

Thanks for this! Helps a bunch!