# Using SumIf function in a macro extract summative data from one worksheet and copy to another.

#### GriffinsPal

##### New Member
Hi all-
First, thank you to all who have helped me with various stuck points thus far. I have learned alot but have also realized how much more there is to know.... par for the course I guess.
Hoping one of you wizards will have a few minutes to help me out on my latest sticking point. I think it is pretty straight forward, but I am a little stuck.

I have a workbook that has several worksheets. Each worksheet contained data for an entire year, with each row containing a date and then relevant data associated with that date. Some worksheets contain data that has similar data on another worksheet (Number of trainees for example) other worksheets have data that is only found on that single worksheet (# of people served, for example).

What I want to do is write a macro that will extract certain elements by month(those referenced above as an example) and sum them across the entire worksheet (for those elements that are only found on a single worksheet), or sum them across all worksheets in which they appear, and then copy those sums to a table in a different worksheet ("totals").

If I were just doing this within an excel file I would write a formula in the respective cells within the "totals" worksheet using the SUMIF function, but because this file will be used by many different users, I worry that the formulas could be inadvertently corrupted, so I thought writing a Macro and activating through a command button would be safer would be better

If I were to write it as a single function for a single worksheet that will sum data for January I think it would be as follows:
=SumIf(C3:C150,1,F3:F150) where the first range is the range in which the month data is located, "1" is the month of january, and the second range contains the data elements I want added. (data on all sheets start in row3, but the last row of data will be different in each sheet. I don't expect any sheet to have more than 150 rows of data, but I expect there is a way of detecting the last row of data and including only that much in the ranges referenced above)

How can I avoid writing a new line of code for each month for which I am trying to get a total?

Also, what would I have to do to extend this formula across more than one workbook to, say, add # of trainees recorded on worksheet (WS1)to the # of trainees recorded on another worksheet (WS2) and then assign that sum to a particular cell (let's say B4) in worksheet 10?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Replies
5
Views
506
Replies
4
Views
199
Replies
1
Views
82
Replies
1
Views
95
Replies
0
Views
72

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### 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.

### Which adblocker are you using?

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

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