Importing data from multiple files to new file

**agengler11** · 11-09-2012, 02:31 PM

Hi all. I am trying to import data from multiple spreadsheets to a new file. I put all of my files in one folder. Each file is exactly the same as far as where the data is. I want the data in the new file to be in a nice clean line by line format. The multiple files have data all over the place.

See example: (Attached File)

I can make my own headers so I would like to keep the A1-Z1 row empty in the new file so we should start populating data with B1

So in short I want to make information on the first file it pulls in to go on A2 B2 C2 .........ETC all the way across
and the next file to go on A3 B3 B3.........ETC

Thanks in advance for your help.

**arlu1201** · 11-09-2012, 02:40 PM

You will need to specify what data goes where.

**agengler11** · 11-09-2012, 02:42 PM

Ive been playing with this script:

Please Login or Register  to view this content.

**arlu1201** · 11-09-2012, 02:44 PM

Ok, all you need to tell me is which cell of your source file goes where in the master file and i can help you with the code.

Either you put out the list here or attach a sheet with the info.

**agengler11** · 11-09-2012, 02:45 PM

I see where is has the range to copy

Please Login or Register  to view this content.

I also understand that in the code its going to paste to the "B" coulumn

Please Login or Register  to view this content.

But the only thing I cant figure out is how to do this many times over in the script.

**agengler11** · 11-09-2012, 02:48 PM

D4 Would go in the B Column
D5 Would go in the C Column
D5 Would go in the D Column
D6 Would go in the E Column
D7 Would go in the F Column
D8 Would go in the g Column

**agengler11** · 11-09-2012, 02:49 PM

I need to move about 20 cells total

**arlu1201** · 11-09-2012, 02:52 PM

So you will need to specify all the 20 here, otherwise i wouldnt know which goes where.

**agengler11** · 11-09-2012, 02:55 PM

I am assuming that if you map out the ones I gave you and you told me which parts to copy and paste I can save you some time? Otherwise my pleasure. Let me know.

**arlu1201** · 11-09-2012, 02:58 PM

The reason i asked you for the list, is - sometimes we dont have to specify each cell, if they are consecutive or can be transposed, the macro can be designed that way.

**agengler11** · 11-09-2012, 03:03 PM

OK Ill tell you..Give me a moment please.

**agengler11** · 11-09-2012, 03:12 PM

D4 Would go in the B Column
D5 Would go in the C Column
D5 Would go in the D Column
D6 Would go in the E Column
D7 Would go in the F Column
D8 Would go in the G Column
N4 Would go in the H Column
N5 Would go in the I Column
N6 Would go in the J Column
N7 Would go in the K Column
N8 Would go in the L Column
N9 Would go in the M Column
N10 Would go in the N Column
N11 Would go in the O Column
RANGE A20:N20 Would go in the P Column
RANGE A21:N21 Would go in the Q Column
RANGE A22:N22 Would go in the R Column
RANGE A23:N23 Would go in the S Column
J29 Would go in the T Column
L69 Would go in the U Column
M69 Would go in the V Column
O69 Would go in the W Column
P69 Would go in the X Column
D92 Would go in the Y Column

**arlu1201** · 11-10-2012, 10:54 AM

2 pointers -

RANGE A20:N20 Would go in the P Column
RANGE A21:N21 Would go in the Q Column
RANGE A22:N22 Would go in the R Column
RANGE A23:N23 Would go in the S Columns

You want multiple columns to be copied into 1 column. How do you want this to be done? Should i use any data separator?

Also,

D5 Would go in the C Column
D5 Would go in the D Column

You want D5 to go both into C and D columns?

**agengler11** · 11-10-2012, 11:08 AM

The D5 was a typo. Im sorry I typed it twice. As far as the multiple cells...just jam them each all into one column please.

**arlu1201** · 11-10-2012, 11:10 AM

What should be the separator for the data?

**agengler11** · 11-10-2012, 11:27 AM

Originally Posted by arlu1201

What should be the separator for the data?

Please use a colon ":"

**arlu1201** · 11-10-2012, 11:54 AM

Originally Posted by agengler11

The D5 was a typo. Im sorry I typed it twice. As far as the multiple cells...just jam them each all into one column please.

What should go into column D?

**agengler11** · 11-10-2012, 11:59 AM

Can you move everything up a letter? If not you can leave blank.

**arlu1201** · 11-10-2012, 12:27 PM

Try this code - put it in a blank workbook which is your summary book.

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Let me know if you want the macro to insert the headers for you. You can put them in the code - i can help you put them in the correct place.

**agengler11** · 11-11-2012, 11:38 AM

Thank you very much for helping me!! I will let you know on Monday.

Importing data from multiple files to new file

LinkBack

Thread Tools

Rate This Thread

Display

Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Re: Importing data from multiple files to new file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions