Need help trying lookup data from multiple sheets into a single master sheet

[Ezz9125]

Hope you're all having a great day,

So, i have multiple sheets named from "sheet (01)" to "sheet (65)"
and i have a master sheet with this headers

Product Title #6​

Description #9​

Product Title(AR) #100006​

Description(AR) #100009​

Images​

SKU​

the problems is not all the sheets have the product title at the same "c1" for example, Sometimes its different same with the others headers too.

im trying to look up all the headers from the multiple sheets and return their data to sum all of them into the master sheet, making sense?

doing this manually means i will open "sheet (01)" copy the "Products Title #6" column into the master sheet then all the other columns as well,

so i was thinking of using MATCH, to match the headers in the master sheet with the headers inside all other sheets and return their data to sum all of them into a single sheet.

but i severely lack the technical knowledge to do so..

thanks for any help in advance : D

 

[Jeffrey Mahoney]

First of all, welcome to the forum!!

You're asking for a few things. Not saying it's difficult. We just need some information.

Are you trying to use VBA? Or are you trying to create a formula to account for all the variations?

 

[Jeffrey Mahoney]

Do you open one workbook at a time and get the values, or are you trying to get values from all open workbooks.

 

[Jeffrey Mahoney]

I'm thinking it would be easier to rearrange your data and then create formulas to cover the columns in the needed locations.

 

[Ezz9125]

thank you : D

I would prefer the easiest or quickest method for sure

the manual way would be opening each sheet and get the date from it, but i was looking if there is a method i can automate it since it will take quite some time to open and close 65 sheets and copy or vlookup data from them

i was thinking of creating a formula or each header that applies to the first sheet (01) then auto fill it for all available rows, then change the sheet number to (02) if that step can't be automated

 

[Ezz9125]

Here is what the master sheet looks like

And here is what a sample sheet looks like (with all the irrelevant columns hidden)

the easiest way i can think of is vlook up with the SKU (but i don't know how to do that if the sku actually comes after the data being looked up)
but even if that will be as slow as i just manually copy and paste them

 

[quanglyvn]

I think can use vba to loop through the sheets looking for the title position, then copy the data to the main sheet

 

[Jeffrey Mahoney]

the manual way would be opening each sheet and get the date from it, but i was looking if there is a method i can automate it since it will take quite some time to open and close 65 sheets and copy or vlookup data from them

First of all, lets get our names on the same track. I use sheet to talk about the tabs at the bottom. I use workbook to talk about different excel files, each with a series of sheets in them.

If you are opening different workbooks with headers in different locations and trying to copy the data, we are going to have to create a macro.

If you have all your data in one workbook but in different sheets, then we need to create very special formulas, or rearrange the columns to be consistent in all the sheets.

 

[Ezz9125]

Oh, well different workbooks then

meaning I have 65 excel files, each contain just one sheet with tens of headers but those 6 in the master workbook are named exactly the same in all workbooks
so the idea is to merge or sum all the rows from all the workbooks into a single master one

 

[Ezz9125]

Thank you for your help, i have found an add-on that does exactly this, by finding all columns headers in all workbooks for me and combining them in one.
not sure if i can mention its name or not here so i won't.

so its solved, thank you xD