Macro for opening files and copying data

May 2, 2011 at 05:49:36
Specs: Windows 7
Hi everyone,

Let me describe my scenario a little in detail:

I have ten files with different names all placed in a single folder and a master file consolidating all the data in a different format.

What I need to do is:

open the first file -> copy the data -> paste it in the master file -> close the first file -> open second file -> copy the data -> paste it in the master file -> close the second file...... and so on for all ten files.

I need a macro stored in the master file that would carry out this activity. Please help.

Many thanks.

Bharat


See More: Macro for opening files and copying data

Report •

#1
May 27, 2011 at 04:26:45
Hi Bharat,

Ok this is possible, but you didnt specify what you are using, ie word, exel etc?


Report •

#2
May 27, 2011 at 09:12:37
Not only didn't you specify what application you are using, you didn't give us any details as to what we are copying or where it's being pasted to.

For example, if it's Excel, then what sheets/range needs to be copied and where in the master file should it be pasted?

BTW...if it is Excel, why can't you link the master file to the other workbooks so you don't have use a macro?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
July 27, 2011 at 02:09:33
All,

Apologies for not providing entire info....

I am trying to copy files from excel to excel.... the range (in each file) will be B4:CU99 and this will be pasted to the master file -> sheet3 -> A1:CT96.

After this I run a macro (already developed) to take the data in sheet3 and place it in sheet1 in the following order...:
Column A (sheet3) goes to Column A (Sheet1) (A2:A97)
Column B (sheet3) comes below in Column A (Sheet1) (A98:193)
.... and so on....
(Hope this is clear)....

I explored the option of linking the masterfile to the workbooks, but that increases the file size to more than 3 times.... :(


Report •

Related Solutions

#4
July 27, 2011 at 05:05:56
re: "After this I run a macro (already developed)"

If the macro is already developed, what are you asking for help with?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 27, 2011 at 05:37:47
To help clarify things

When info is copied from workbook1 B3:CU99, what sheet is it coming from.
When info comes from workbook1 it is placed on sheet3 to the master workbook sheet 3 B1:CT6 (is it really CT or CU)?
When info comes from from workbook2 and so on to workbook 10, where does each of the numbered workbooks need to paste its data?

I sounded like you ran an already developed macro AFTER the copy and paste of all 10 workbooks was complete.

This is all very "doable", its just a matter of being a little more precise of with the references to where data is coming from and where data is going to. It helps the VBA programers immensly since they can't see your work book or read your mind to fill in missing info.

I unfortunately cannot help with VBA (Macro btw) but I know what the experts here will need to a degree.

I hope this helps.


Report •

Ask Question