Excel - Copy Totals to Week # Macro: Episode 1470

1,976 views · Published 14 November 2011 · 6:30 · Indexed 23 October 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Efficient Macro to Copy Totals to Specific Week Number Row.

Welcome to episode 1470 of the MrExcel podcast. In this episode, we will be discussing a macro that allows you to copy totals to a specific row for a week number. This is a great solution for those who have a weekly entry workbook and need to transfer their totals to a summary page. 

This macro was inspired by a question from Patrick, who was looking for a more efficient way to transfer his totals to the summary page. He was manually selecting the row where the totals should go, but knew there had to be a better way. After some experimentation, I have come up with two solutions for this problem. 

The first solution involves using the find command, which can be a bit tricky and can cause problems if the settings are not properly adjusted. The second solution is to loop through all the cells, which is a bit more time-consuming but can be more reliable. In this video, we will be using the find command to locate the row where the totals should go. 

To make this macro even more versatile, I have also added a feature that allows it to account for any extra blank rows that may be added in the weekly entry sheet. This way, the macro will still work even if the layout of the sheet is changed. 

The code for this macro involves defining two variables, one for the weekly entry sheet and one for the summary sheet. We then use the find command to locate the row where the totals should go, based on the week number entered in cell E2. Once the row is found, we use the copy and paste special functions to transfer the totals from the weekly entry sheet to the summary sheet. 

One thing to note is that if the week number is entered incorrectly, the macro will not be able to find the correct row and an error will occur. However, this can easily be fixed by double-checking the week number before running the macro. 

I hope this macro will be helpful for those who have a weekly entry workbook and need to transfer their totals to a summary page. Thank you to Patrick for sending in this question and thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe and hit the notification bell to stay updated on all our latest episodes. See you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Copy Totals to Proper Week
(00:19) Explanation of the Workbook
(00:31) Desired Outcome
(00:41) Two Ways to Solve the Problem
(00:51) Using the Find Command
(01:15) Looping Through Cells
(01:26) Using the Find Command with Adapted Code
(02:03) Defining Variables
(02:13) Finding the Week Number
(02:45) Copying and Pasting Values
(03:16) Running the Macro
(03:45) Paste Special and Transpose
(04:59) Copy Totals Macro
(05:24) Testing the Macro
(05:55) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Copy and paste special
Copy to row for week number
Episode 1470
Expense categories
Find command
Learn Excel
Loop through cells
Macros
VBA
Weekly entry


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153148/


Patrick has a weekly Expense Ledger Spreadsheet set up; he wants to create a Macro to copy the Totals from Report 1 to a corresponding Week Number Row on the Summary worksheet. This Macro has to use the FIND command in VBA to figure out where to paste special the data. Follow along with Episode #1470 as Bill shows us how to accomplish the task.

More from this channel