Macro to pull and calculate daily data to weekly report

diripio

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?

RegionWk 1Wk2Wk3Wk4Wk5
Korea
Japan
China
India
Israel
Russia
Denmark
Ireland
England
Pacific Islands
East
West
North
South
Grand Total$0$0$0$0$0



Region3/1/20213/2/20213/3/20213/4/20213/5/20213/6/20213/7/20213/8/20213/9/20213/10/20213/11/20213/12/20213/13/20213/14/20213/15/20213/16/20213/17/20213/18/20213/19/20213/20/20213/21/20213/22/20213/23/20213/24/20213/25/20213/26/20213/27/20213/28/20213/29/20213/30/20213/31/20214/1/20214/2/20214/3/20214/4/20214/5/20214/6/20214/7/20214/8/2021Grand Total
Korea59,29961,87759,24659,84560,14859,57459,80061,86764,48164,56964,56564,81363,02062,73964,03367,15666,59566,98766,34064,45063,84465,76267,03967,07967,51666,98764,10763,19664,91167,12667,96469,90069,25369,35268,40671,14374,23575,32815,5412,500,091
Japan2,1431,9822,0102,0242,0292,0072,0072,0272,0302,0722,0822,0622,0632,0732,0482,0712,0892,1082,1442,2272,2152,2222,2332,2352,2112,2102,2062,2062,2202,3162,3152,3682,3352,3932,3932,4602,7092,66453283,742
China445,193489,549452,443437,984426,414403,828400,972413,129431,986458,040443,399442,569434,545430,232493,087430,587423,101431,420433,065429,011420,584424,042427,401429,738430,555431,346422,820420,705424,263422,339424,225420,894428,729423,281415,549423,298428,223396,95342,06816,407,566
India166,041167,429170,808177,850171,862174,631172,476171,077174,657171,999171,662171,038175,790176,409181,867180,272176,531179,480179,132177,649174,850174,969177,956175,145176,043172,547169,479171,487173,606175,364178,036179,970179,164176,472176,407179,754178,588175,05432,0456,685,596
Israel3,5093,5933,5743,6713,7223,5543,5543,5303,4713,4183,4563,5753,5043,5253,5323,5693,5203,6503,8343,6193,5923,9843,3863,4003,1913,1593,2383,1713,3183,2523,2773,3113,3743,1943,1843,2673,2742,977430131,356
Russia175,671181,160181,449189,881184,281180,263180,448190,694205,121206,183212,286217,404219,914202,105197,328193,269202,788203,276200,507195,294197,235194,580191,080196,064205,805192,783191,977188,613188,689226,287196,354204,980201,196194,642194,590198,563201,252189,38832,8207,506,217
Denmark60,92760,95762,63462,30663,38963,30561,17662,11861,41561,46060,99661,91161,59361,22760,73760,08162,18163,36464,54964,36461,59261,90164,59564,31265,87067,33565,22563,99163,62462,25662,00963,15063,14463,14662,75163,22364,68964,45211,0672,399,022
Ireland17,85420,37020,95021,48920,72020,66920,71222,48823,30622,12922,79922,08821,53921,80421,40921,59222,57621,98721,76421,39021,46821,71822,62622,50421,18821,00820,79020,65221,85322,12420,76722,02322,85823,89924,01824,22722,18320,6211,308827,468
England168,168165,011166,936172,460164,487161,237163,719152,771151,214151,634139,025131,545125,832127,083127,502128,852127,693129,535129,216127,146129,719128,961127,473127,002126,339127,867131,327129,475132,075137,758141,688144,400142,066137,940134,618138,136135,908125,11822,2945,331,231
Pacific Islands41,68035,55934,45235,29934,05033,87033,78133,83833,73533,78434,77834,99034,65234,56235,25335,36135,83037,46937,15336,65137,00537,12938,41939,19038,96538,45738,62138,73840,72140,79140,45341,26042,53840,72540,36138,84939,09833,3265,1991,416,594
East92,33694,19393,43193,73793,56389,61088,61089,32689,01289,76890,12692,60793,00292,85092,18191,92591,13291,10692,79391,74192,08291,97291,10491,37292,61194,34492,47592,21792,49493,68894,89495,06195,95495,94997,06498,56197,20080,39111,6753,524,157
West173,325173,313173,564175,634175,010162,088153,955175,499176,134175,698177,182181,913181,256180,417183,754185,204185,695177,713182,712182,184179,959181,970184,480183,276181,574191,357194,937190,805189,886189,833191,985180,329174,103172,271171,013173,596175,911152,03020,3776,811,942
North78,16476,78475,26278,79581,35678,54978,01978,14778,30978,09777,85079,58378,47277,77578,11178,04179,22379,17475,75765,20260,16761,03658,39657,78757,43657,33157,88357,87257,87159,08758,23058,44758,86258,64558,62658,83959,19252,1656,9692,605,511
South55,74355,81355,70657,00857,01755,26055,34756,02556,37555,72555,86556,65255,96555,77356,10857,43358,51160,08959,97553,35452,35351,01648,93149,69650,13153,76353,04554,02654,54654,12154,16252,82454,47253,62553,97555,37555,49952,8968,4152,092,613
Grand Total1,540,0521,587,5901,552,4641,567,9821,538,0481,488,4461,474,5781,512,5341,551,2471,574,5781,556,0711,562,7501,551,1471,528,5751,596,9511,535,4121,537,4651,547,3571,548,9401,514,2831,496,6641,501,2631,505,1191,508,7981,519,4361,520,4941,508,1321,497,1541,510,0781,556,3401,536,3601,538,9151,538,0461,515,5331,502,9551,529,2911,537,9601,423,363210,73758,323,106
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top