VBA Extract data from one Workbook and Transpose Paste to another

[Boomer1962]

This is my first post so please bear with me. I have only a little experience with VBA coding.
I have no coding done so far because I do not know where to start.
I have workbooks containing one sheet of data only. I will be opening these one by one and performing the extract before moving to the next one.
All the worksheets are laid out the same way but, the number of columns varies from one to the next. The first 2 title columns contain store number and name in rows. The intermediate columns contain a varying number of "sizes" and some "sizes" may repeat with the quantities per store as data below that. The last columns are totals. The number of totals columns can vary from 1 to ?? depending on unique values in the intermediate column headers. The totals columns group together all columns with the same "sizes" and the data in those columns is the sum of all the columns with the same sizes.
I need to extract the totals columns one at a time into a separate workbook (let's call this workbook the "Summary"). The "Summary" workbook is arranged "transposed" meaning that each column's data from the source workbook will add a row to the "Summary" workbook.
First I need to find the position of each Totals column and copy. Then I need to paste "transposed" into the destination row.

I understand this is a big ask but any examples to guide me would be of help.
Thank you

 

[DanteAmor]

Hello @Boomer1962.
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

To help you with a macro, it is necessary that you give examples of how the structure of your data is in the sheets of your books.
And, very important, the result you want.

Look at the example of this post, the poster put examples of how the data is in his sheets and put 2 examples of the results he wants.

match based on headers and cell and sheet name to summing amounts

Hello I need populating sheets names in column A and I will write in cell C1 the month and headers B2:E2 across sheets so if I write month in C1 JAN , then should populate list of sheets names in column A and match headers and should sum the amounts for each sheet...

www.mrexcel.com

He was so precise in his examples that the macro worked first time.

It is not necessary that your examples contain real data, they can be fictitious data, the important thing is to see the real location of the data, in which row it starts, in which column it starts. If a title must be searched, then that title how do I find it on the sheet, with lower case, with upper case, etc.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[Boomer1962]

This is the source file:

rQtC3A9s5FAffichage_Coup_de_coeur_TAG_ENDLESS_SUMME__R FEMME+ TOTEM Tag Endless 0-16-Fete des peres.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	CC TAG ATHLETICA GOLF TENIS
3	Titre du PDF		FR	ANG	FR	ANG
4	Grandeur		22x28	22x28	22x28	22x28	11x17	11x17	11x17	11x17	11x17	11x17	11x17	11x17	11x17	11x17	22x28	11x17
5	Matériel		Styrène	Styrène	Styrène	Styrène	Papier	Papier	Papier	Papier	Papier	Papier	Papier	Papier	Papier	Papier
6	#	MAGASIN
7	3	Mont-Royal	2		2		1	1	1	1	1	1	1	1	1	1	4	10
8	6	Shawinigan	2		2		1	1	1	1	1	1	1	1	1	1	4	10
9	7	Trois-Rivières					1	1	1	1	1	1	1	1	1	1	0	10
10	8	St-Jérôme	2		2		1	1	1	1	1	1	1	1	1	1	4	10
11	9	Boisbriand	2		2		1	1	1	1	1	1	1	1	1	1	4	10
12	11	Val D'Or	2		2		1	1	1	1	1	1	1	1	1	1	4	10
13	12	Gatineau	2		2		1	1	1	1	1	1	1	1	1	1	4	10
14	14	Repentigny	2		2		1	1	1	1	1	1	1	1	1	1	4	10
15	15	Rimouski	2		2		1	1	1	1	1	1	1	1	1	1	4	10
16	17	Rouyn-Noranda					1	1	1	1	1	1	1	1	1	1	0	10
17	18	Riv.-du-Loup	2		2		1	1	1	1	1	1	1	1	1	1	4	10
18	21	Amos	2		2		1	1	1	1	1	1	1	1	1	1	4	10
19	23	La Sarre	2		2		1	1	1	1	1	1	1	1	1	1	4	10
20	27	New-Richmond	2		2		1	1	1	1	1	1	1	1	1	1	4	10
21	28	Sorel	2		2		1	1	1	1	1	1	1	1	1	1	4	10
22	29	Valleyfield	2		2		1	1	1	1	1	1	1	1	1	1	4	10
23	30	Chicoutimi	2		2		1	1	1	1	1	1	1	1	1	1	4	10
24	31	St-Jean	2		2		1	1	1	1	1	1	1	1	1	1	4	10
25	34	St-Constant	2		2		1	1	1	1	1	1	1	1	1	1	4	10
26	35	GreenfieldPark	2		2		1	1	1	1	1	1	1	1	1	1	4	10
27	36	LaSalle	4		4		1	1	1	1	1	1	1	1	1	1	8	10
28	38	Châteauguay	2		2		1	1	1	1	1	1	1	1	1	1	4	10
29	39	Granby	2		2		1	1	1	1	1	1	1	1	1	1	4	10
30	40	Sept-Îles	2		2		1	1	1	1	1	1	1	1	1	1	4	10
31	42	Baie Comeau	2		2		1	1	1	1	1	1	1	1	1	1	4	10
32	43	Neufchâtel	2		2		1	1	1	1	1	1	1	1	1	1	4	10
33	44	Mascouche	2		2		1	1	1	1	1	1	1	1	1	1	4	10
34	45	Québec	2		2		1	1	1	1	1	1	1	1	1	1	4	10
35	46	Victoriaville	2		2		1	1	1	1	1	1	1	1	1	1	4	10
36	47	Laval	2		2		1	1	1	1	1	1	1	1	1	1	4	10
37	49	Beauport	2		2		1	1	1	1	1	1	1	1	1	1	4	10
38	50	Ste-Foy	2		2		1	1	1	1	1	1	1	1	1	1	4	10
39	58	St-Hyacinthe	2		2		1	1	1	1	1	1	1	1	1	1	4	10
40	60	Cap-de-la-Madeleine	2		2		1	1	1	1	1	1	1	1	1	1	4	10
41	61	Sherbrooke	2		2		1	1	1	1	1	1	1	1	1	1	4	10
42	65	St-Bruno	2		2		1	1	1	1	1	1	1	1	1	1	4	10
43	68	Drummondville	2		2		1	1	1	1	1	1	1	1	1	1	4	10
44	70	Mont-Laurier	2		2		1	1	1	1	1	1	1	1	1	1	4	10
45	72	Marché Central	2		2		1	1	1	1	1	1	1	1	1	1	4	10
46	73	Hull	2		2		1	1	1	1	1	1	1	1	1	1	4	10
47	75	Bathurst	2		2		1	1	1	1	1	1	1	1	1	1	4	10
48	97	Joliette	2		2		1	1	1	1	1	1	1	1	1	1	4	10
49	102	Orléans	0	2	0	2	1	1	1	1	1	1	1	1	1	1	4	10
50	501	Lévis	2		2		1	1	1	1	1	1	1	1	1	1	4	10
51	502	Anjou	6		6		1	1	1	1	1	1	1	1	1	1	12	10
52	TOTAL		90	2	90	2	45	45	45	45	45	45	45	45	45	45	184	450
CC TAG ATHLETICA GOLF TENIS

Cell Formulas
Range	Formula
Q4:R4	Q4	=UNIQUE(C$4:P$4,TRUE)
Q7:R52	Q7	=SUMIFS($C7:P7,$C$4:P$4,Q$4#)
C52:P52	C52	=SUM(C7:C51)
Dynamic array formulas.

 

[Boomer1962]

This is the destination file...partial.

Tableau Synthèse de Facturation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2					3		6		7		8		9
3					Mont-Royal		Shawinigan		Trois-Rivières		St-Jérôme		Boisbriand
4	Nom Promo	Format	Prix		Qté	Sous-Total	Qté	Sous-Total	Qté	Sous-Total	Qté	Sous-Total	Qté	Sous-Total
5						- $		- $		- $		- $		- $
6
7
8
9
10
11
12
13
14
Synthèse

Cell Formulas
Range	Formula
F5,N5,L5,J5,H5	F5	=+E5*$C$5

 

[DanteAmor]

Thanks for the example and for doing it with the XL2BB tool, but unfortunately it's not complete.

And, very important, the result you want.

Just like commenting on post#2, it is very important that you post the results. The sheet you put with results is empty.
You must put the results, and I am going to ask you something else, explain how to obtain those results.

An example is too little, considering that you mention that the sheets in the books contain different sizes.
So put at least 3 examples and the results of each example.

 

[DanteAmor]

I have workbooks containing one sheet of data only.

I have other questions:
Please answer each question very briefly.

1. Do you want the macro to work only for one file?
2. If it's only for one file, will the file already be open?
3. If the file is not open, do you want the macro to open the file?
4. How do you want me to open the file, is it a fixed name?
5. Do you want the macro to open windows explorer and you select the file?
6. If there are several files, in which folder are the files?
7. If you are going to process all the files in the folder, where should you put the output for each file?

All these data are necessary to help you.