VBA Macro For Creation of Summary File from Workbooks

[structo]

Hi Friends,

I have different workbooks. and i want to extract some data from these files to one summary file. please find data workbooks, sample summary file and please share workable code (data range from files are mentioned in " Summary File sample" workbook).


Thank you in advance friends,

[Mumps1]

Make sure that all workbooks are open. Place this macro in a regular module in the "Summary" file and run it form there.

Please Login or Register  to view this content.

[structo]

Make sure that all workbooks are open. Place this macro in a regular module in the "Summary" file and run it form there.

Dear friend,
thank you for sharing nice code.i need by without opening all files and with help of selecting by folder by popup box. My folder Location is "D:\Final" (folder name is "Final"). any possibilities?

kindly arrange small modification.

Thank you.

[Mumps1]

What are the actual names of the files you will be selecting? Will you always be selecting the same 3 files? In other words will you always select File1.xlsx, File2.xlsx and File3.xlsx or will these names change over time?

[structo]

Friend,
Those are always same names as File1,File2,File3. but file extensions are ".xlsm". in rare situations i will change those file names.

Thanks.

[Mumps1]

So the file names are File1.xlsm, File2.xlsm and File3.xlsm. Is this correct?

[structo]

So the file names are File1.xlsm, File2.xlsm and File3.xlsm. Is this correct?

Yes Friend, Correct.

[MarvinP]

Hi structo,

Excel has a newer tool, starting with 2010 that does what you are asking for. The Add-In is called "Power Query" in 2010 and 2013 and "Get & Transform" in 2016 Excel. If you have the data in your Files in "Tables" this newer tool can be used. See an example at:

https://support.office.com/en-us/art...c-6279bef08df4 or
http://whitepages.unlimitedviz.com/2...n-power-query/

[structo]

Hi structo,

Excel has a newer tool, starting with 2010

Sorry Friend, right now i am used 2003 and 2007 versions only.looking for Code.

Thanks.

[Kenneth Hobson]

Similar to Mumps1's code, run from a backup copy of the master file with the sheet to fill being active.

Please Login or Register  to view this content.

[structo]

Similar to Mumps1's code, run from a backup copy of the master file with the sheet to fill being active.

Dear Sir,

Your code is really marvelous. Thank you for sharing, Thank you all for yours support.

[structo]

Dear Sir,

I am facing small problem with formulas. because values are not pasted like a "Paste special" (some times cells contains formulas). what is the modification for paste special?

Thanks.

[structo]

Friends,

How to convert as a Paste special Values Developed (Only Cell values not Formulas)? because pasted as a formulas instead of Cell Values.

Thanks in Advance.

[Winon]

Hello structo,

How to convert as a Paste special Values Developed? because pasted as a formulas instead of Cell Values.

Do you want to Paste Formulas or Values?

Regards.

[structo]

Dear friend, i need Only paste Values (Like a Paste special and Values).


Thank you friend.

[Winon]

Thank you for the feedback structo.

Let us take the Code from Mumps1 as changed by Kenneth Hobson, and try it as follow;

Please Login or Register  to view this content.

Regards.

[structo]

Thank you for the feedback structo.

Let us take the Code from Mumps1 as changed by Kenneth Hobson, and try it as follow;

Thank you for Modification Friend,i hope now all values developed as Copy and Paste values.

I will test and Give Feedback.

Thank you very much.

[structo]

Thank you for the feedback structo.

Let us take the Code from Mumps1 as changed by Kenneth Hobson, and try it as follow;

Regards.

Dear friend,

After running code, from file3 values are not developed in summary file.

Thanks.

[Winon]

Sorry structo,

I seem to have missed a few lines in tweaking the Code. Please try the below now.

Please Login or Register  to view this content.

[Kenneth Hobson]

Doing what you want in #15 can be done by PasteSpecial(). If you don't know how, that is what the macro recorder is for. It is your friend.

Here is the first one with values and number formats as most would want that. I leave it to you to modify the other ranges.

Please Login or Register  to view this content.

Use xlPasteValues if that is what you really wanted.

The other method to transform formulas into values is:

Please Login or Register  to view this content.

[structo]

Sorry structo,

Thank you friend, still not developed full values by your code.please test with my sample files.

Thanks.

[structo]

Use xlPasteValues if that is what you really wanted.

The other method to transform formulas into values is:

Thank you sir, i am changed as below code type range and works fine.

Please Login or Register  to view this content.

and what is the modification for below Code for Paste Special:

Please Login or Register  to view this content.

i am studied below method and very interesting, as per below code how to use? where should i paste?

Please Login or Register  to view this content.

During using of code some hang is happened in my excel file, how to minimize? after popup, if i have chooses "Cancel" button still my excel work book mouse cursor is visible like a background process animation and hangs. kindly tweak the code.


Thank you Sir.