Need to move data from one sheet to another

[Lizzi0001]

I have ten spreadsheets that are filled in daily by reps to track productivity. Each spreadsheet has a monthly totals sheet in it to add up the reps totals. I would like to be able to move each reps monthly total to one sheet to track the department as a whole. There would be 10 monthly totals sheets to be added together. All sheets are in the same folder and in the same format, the tabs inside the sheets are the same. The only difference is that the sheets each have the reps names on them. I have been trying to figure out the formula but cannot. can it be done this way without the use of a VBA macro? and if it can, can you help with the formula?

Thank you,
Lizzi

[GeneralDisarray]

just out of curiosity, is a VBA macro not a possibility for some reason or would you just prefer to not use one. I have no idea how to do it without a macro, but it's really simple with one

[Lizzi0001]

just out of curiosity, is a VBA macro not a possibility for some reason or would you just prefer to not use one. I have no idea how to do it without a macro, but it's really simple with one

I have no idea how to use VBA....I have no training in it, which is why I am trying to do it without that.

[Hurricanefly]

Would the totals for each month be in the same cell for each spreadsheet?

[GeneralDisarray]

Well you came to the right place for help on this then
This is a very common request, there are many examples on this forum showing this (with macros).


Try out the example I attached, if it's impressive enough to make you want to try macros I'll see what I can do to help.


1) download the zip file (test.zip)

2) unzip the file, this will give you a folder called "test"

3) open that folder and open the workbook insided named "MasterSheet_WithMacro"

Enable macros, then press the "Make it Happen!" button.


That will launch the macro which will then enter the sub-folder component sheets and perform some copying of the data found there. This process can be altered to work on your specific sheets also.

regards

[Lizzi0001]

Well you came to the right place for help on this then
This is a very common request, there are many examples on this forum showing this (with macros).


Try out the example I attached, if it's impressive enough to make you want to try macros I'll see what I can do to help.


1) download the zip file (test.zip)

2) unzip the file, this will give you a folder called "test"

3) open that folder and open the workbook insided named "MasterSheet_WithMacro"

Enable macros, then press the "Make it Happen!" button.


That will launch the macro which will then enter the sub-folder component sheets and perform some copying of the data found there. This process can be altered to work on your specific sheets also.

regards

Thank you for your help. I appreciate it. I followed the instructions but the macro gave me the following error. RUNTIME ERROR 1004: the following idrectory cannot be found.


To answer your question: yes my sheets are identical and all fields are exactly the same on the sheets. We use them to track individual rep productivity and I am trying to get an easy way to put them all together and track the department as a whole.

I will attach a copy sot hat you can seee what I am doing. Although there are 32 tabs on each rep sheet, the only piece needed in be moved over is the totals from the monthly totals page.

[GeneralDisarray]

sorry to hear the example failed, the workbook needed to be in the same directory as the "componentSheets" folder. Maybe it wasn't? Don't worry about that too much however, I'll make something for you based on the sheets you uploaded.

Let me have a look and I'll get back to you

[GeneralDisarray]

attached is a go at this.

Note, to make it happen i had to unlock the sheet you sent. this now has a password of "pword" and you can change that by editing the macro (can explain how to do that later).


basically, you mentioned the worksheets from individual people will sit in a single folder. This makes use of that, loops through the folder and accumulates the "montly totals" numbers.

1) save the attachement
2) unzip it
3) open the master sheet - leave the sheet in the directory that also contains the "ComponentSheets"
** enable the macro content or nothing will work **

4) press alt+f8 to see the macro in a list
5) select the macro (collectNumbers) and press Run

Let me know how it goes

[Lizzi0001]

I just wanted to say thank you very much. The VB Macro that you designed for me worked exactly as I needed it to. I appreciate your time and effort.

[GeneralDisarray]

great to hear thanks for the follow up!