Macro to Consolidate Data from Various Excel Files
 

Hello All Excel Gurus

I am not an expert of Macros or VBA, but I think there can be a macro based solution to my problem for sure.

I have to prepare a report every month in which I have to open around 100 spreadsheets which all have exactly same format and worksheets inside them. And go to a particular worksheet and copy the data from Coloum A to Coloum K in a seperate sheet. The seperate sheet will thus have the data from all those 100 worksheets.

Is there a macro solution to this. All my files will be in the same folder every month, for example in C:\Users\g.khanna\Desktop\Spain\Recon\Dec'12\445

Also is it possible that in coloum A of the seperate spreadsheet I will have the name of the spreadsheets from which those information have been gathered. I mean, if data has been copied from excel spreadsheet name "2.2.305", then coloum A must states "2.2.305" in all rows which have been copied from this spreadsheet.

Please let me know if more information is required to prepare a solution to this.

Thanks in advance to all excel gurus.

Macro to Consolidate Data from Various Excel Files
 

hi Gaura215,

the sheet name to be read is the same on all files ?
there is a particularity to identify the last row of sheet?

isabelle

Le 2013-01-29 05:33, Gaura215 a écrit :
Hello All Excel Gurus

I am not an expert of Macros or VBA, but I think there can be a macro
based solution to my problem for sure.

I have to prepare a report every month in which I have to open around
100 spreadsheets which all have exactly same format and worksheets
inside them. And go to a particular worksheet and copy the data from
Coloum A to Coloum K in a seperate sheet. The seperate sheet will thus
have the data from all those 100 worksheets.

Is there a macro solution to this. All my files will be in the same
folder every month, for example in
C:\Users\g.khanna\Desktop\Spain\Recon\Dec'12\445

Also is it possible that in coloum A of the seperate spreadsheet I will
have the name of the spreadsheets from which those information have been
gathered. I mean, if data has been copied from excel spreadsheet name
"2.2.305", then coloum A must states "2.2.305" in all rows which have
been copied from this spreadsheet.

Please let me know if more information is required to prepare a solution
to this.

Thanks in advance to all excel gurus.