VBA Extract data from one Workbook and Transpose Paste to another

Boomer1962

New Member
Joined
Jul 4, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
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
--------------​
 
Upvote 0
This is the source file:

rQtC3A9s5FAffichage_Coup_de_coeur_TAG_ENDLESS_SUMME__R FEMME+ TOTEM Tag Endless 0-16-Fete des peres.xlsx
ABCDEFGHIJKLMNOPQR
2CC TAG ATHLETICA GOLF TENIS
3Titre du PDFFRANGFRANG
4Grandeur 22x2822x2822x2822x2811x1711x1711x1711x1711x1711x1711x1711x1711x1711x1722x2811x17
5MatérielStyrèneStyrèneStyrèneStyrènePapierPapierPapierPapierPapierPapierPapierPapierPapierPapier
6#MAGASIN
73Mont-Royal 221111111111410
86Shawinigan 221111111111410
97Trois-Rivières1111111111010
108St-Jérôme221111111111410
119Boisbriand221111111111410
1211Val D'Or221111111111410
1312Gatineau221111111111410
1414Repentigny221111111111410
1515Rimouski221111111111410
1617Rouyn-Noranda1111111111010
1718Riv.-du-Loup221111111111410
1821Amos221111111111410
1923La Sarre221111111111410
2027New-Richmond221111111111410
2128Sorel221111111111410
2229Valleyfield221111111111410
2330Chicoutimi221111111111410
2431St-Jean221111111111410
2534St-Constant221111111111410
2635GreenfieldPark221111111111410
2736LaSalle441111111111810
2838Châteauguay221111111111410
2939Granby221111111111410
3040Sept-Îles221111111111410
3142Baie Comeau 221111111111410
3243Neufchâtel221111111111410
3344Mascouche 221111111111410
3445Québec221111111111410
3546Victoriaville221111111111410
3647Laval221111111111410
3749Beauport221111111111410
3850Ste-Foy221111111111410
3958St-Hyacinthe221111111111410
4060Cap-de-la-Madeleine221111111111410
4161Sherbrooke221111111111410
4265St-Bruno221111111111410
4368Drummondville221111111111410
4470Mont-Laurier221111111111410
4572Marché Central221111111111410
4673Hull221111111111410
4775Bathurst221111111111410
4897Joliette221111111111410
49102Orléans02021111111111410
50501Lévis 221111111111410
51502Anjou6611111111111210
52TOTAL90290245454545454545454545184450
CC TAG ATHLETICA GOLF TENIS
Cell Formulas
RangeFormula
Q4:R4Q4=UNIQUE(C$4:P$4,TRUE)
Q7:R52Q7=SUMIFS($C7:P7,$C$4:P$4,Q$4#)
C52:P52C52=SUM(C7:C51)
Dynamic array formulas.
 
Upvote 0
This is the destination file...partial.
Tableau Synthèse de Facturation.xlsx
ABCDEFGHIJKLMN
236789
3Mont-Royal Shawinigan Trois-RivièresSt-JérômeBoisbriand
4Nom PromoFormatPrixQtéSous-TotalQtéSous-TotalQtéSous-TotalQtéSous-TotalQtéSous-Total
5- $- $- $- $- $
6
7
8
9
10
11
12
13
14
Synthèse
Cell Formulas
RangeFormula
F5,N5,L5,J5,H5F5=+E5*$C$5
 
Upvote 1
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top