Need VBA code for consolidating different worksheets based on the sheet name

[priya568]

Hi All,

Someone please help me out to create a vba excel macro.

Scenerio:
Meena and Raju are two employees with monthly sales data in different workbook and worksheets.

Master data should consolidate the monthly sales data of Meena and Raju in monthwise.

For eg: If I choose 'January' in Master data file and click fetch data. Then code should fetch the data of January sheet of Meena and Raju sales workbook.If I choose 'February' in Master data file and click fetch data. Then code should fetch the data of February sheet of Meena and Raju sales workbook and so on...All data should be consolidated in master data file based on the month chosen

Attached are the files

Thanks,
Priya

 

[Luke M]

Check out Ron's free tool here:
http://www.rondebruin.nl/win/addins/rdbmerge.htm

Sounds like it may do exactly what you want, letting you choose which file(s) you want to merge, and which sheet to merge.

 

[priya568]

Hi,

thanks for the link.

Is it possible to do this in excel vba?

Thanks!
Priya

 

[Luke M]

Yes...which is what the solution I posted uses. An add-in is a special set of macros that have been prepped in such a way to be easily deployed to other computers. You may have noticed that Ron designed a nice interface for his tool, so you don't have to do anything with the code. If you want more control, the parts of code that he uses is here:
http://www.rondebruin.nl/win/s3/win008.htm

 

[priya568]

Hi,

I badly need a vba code to consolidate the sheets of multiple workbooks with the same sheet names to the master data..I have searched in all sites but nothing helped me. still i don't have a clue. :'(

I have attached the expected output - Master sales data. Where you can find the sheet 'January' contains the consolidated sheets of January from the workbooks Meena sales data and Raju sales data.

Some one pls help..

Thanks in advance

 

[Marc L]

Hi,

if you don't want to go to commercial services,
you can start yourself your code by activating Macro Recorder
and do some operation like open a workbook, use worksheet functions
like filter or find, you will get a free base code !

After posting it here, explain a precise technical difficulty …

________________________________________________________
Je suis Charlie
​

 

[rrocker1405]

Hi Priya,

Attached zip file includes the consolidater as required. The below is the step by step approach.

1. All the files have to be in one specific folder only thats when they would work else they will not.
2. The file 'Master.xlsm' has a get data button and this will extract the data of Meena and Raju sheet information and will update for respective months.
3. The sheet will not work if you dont have the functions module so do not delete or edit it.

Presently the extract will pick values from January to March but if you would like to include until December this is how you will have to update the codes.

Go to the module GetData_Data and modify the following code

GetData ThisWorkbook.Path & "\[file name including its extension]", "[Enter month]", _
"[range to copy]", Sheets("[sheet name]").Range("[specify cell to dropvalues"), True, True

The above would mean the code should be something like this as an example

GetData ThisWorkbook.Path & "\Meena sales data.xls", "January", _
"A1:G60000", Sheets("Meena").Range("A2"), True, True

Hope this helps you.

Kind regards,
Anand

 

[hashw]

Hi Team,

Need to consolidate sheets based on Sheet Names or just headers.

The work book has many sheets where headers are different, but I want the collated sheet only with the data from same headers