Combining multiple workbooks from different location in to one sheet.

**safi0102** · 04-11-2013, 03:02 PM

Hi Guys...!!

Hope you all are well..

This forum site is more helpful site for me to fix up the issue's i have and had.. So Thanks to the Admin.

Now let me tell about my issue

.

I just want to combine multiple workbooks in to one sheet which all are in different location and also i dont want to copy the whole workbook data..!!

Each workbook have a Sheet named as Prefix, This sheet value's needs to be combined in one sheet.

I have searched through out this forum and i haven't found any post related to my issue.

Guys can please help me here ?

I have to combine more than 20 files in different location ..!!

Many thanks in advance for all your help and time.

Thanx,
Safi.

**arlu1201** · 04-12-2013, 07:27 AM

When you say different locations, would all the files in those folders need to be consolidated? Also, are all the folders under the main root folder or in different drives?

**safi0102** · 04-12-2013, 07:53 AM

Hi Arlu,

Thansk for your reply..

Yes you are right we need to consolidate all teh workbooks.

All are in same drive but path's are different.

And also the file name (Wokbook) Name will be varying for a time being..

Thanks again,
Safi.

**arlu1201** · 04-12-2013, 08:31 AM

So for e.g. if we consider the file path to be D:\Reports\ then all the files from all the subfolders in this path should be consolidated, right?

**safi0102** · 04-12-2013, 08:38 AM

Nope, Not all teh files.

We will provide teh excat path of the workbook and will need to consolidate them in to a single shhet.

Since there some unwanted files should be thre in teh folders/Subfolders.

Thansk,
Safi.

**arlu1201** · 04-12-2013, 09:54 AM

But you said there are multiple folders. So you will provide each folder from which the data should be consolidated?

**safi0102** · 04-12-2013, 10:11 AM

Yes, You are right.. !!

Thanks,
Safi.

**arlu1201** · 04-12-2013, 10:17 AM

Ok, whats the sheet name containing the file paths and in which column?

**safi0102** · 04-12-2013, 10:32 AM

Thansk Arlu,

Here you go..

Please note taht the given path folder must have only one workbook but the name of teh workbook will differ for each folders.
---------------------------------------------------

Path no: 1

T:\GBCWBRKCS001\NortelSW\GNP_NGNP THUS_YOURCOMMS\BT\GNP\ENERGIS\POI\ABERDEEN (IMPORT)\EXPORT\PDI\CURRENT\MASTER

Pth no: 2

T:\GBCWBRKCS001\NortelSW\GNP_NGNP THUS_YOURCOMMS\BT\GNP\ENERGIS\POI\BANGOR (IMPORT)\IMPORT\PDI\CURRENT\MASTER

Path No: 3

T:\GBCWBRKCS001\NortelSW\GNP_NGNP THUS_YOURCOMMS\BT\GNP\ENERGIS\POI\BOURNEMOUTH (IMPORT)\IMPORT\PDI\CURRENT\MASTER

Path No: 4

T:\GBCWBRKCS001\NortelSW\GNP_NGNP THUS_YOURCOMMS\BT\GNP\ENERGIS\POI\BRADFORD (BOTH)\IMPORT\PDI\CURRENT\MASTER

Sheet Name : Number -- Prefix

Coumn numbers : A to Z

--------------------------------------

The workbook may have mutiple sheets but we want to get onlty teh dat which SHEET named as "Number -- Prefix".

I'm looking forward to hear from you..

Thanks in advance.
Safi.

**arlu1201** · 04-12-2013, 01:47 PM

The sheet is named as "Number -- Prefix" or it has a number and a prefix?

All columns from A to Z have the paths in them?

**safi0102** · 04-12-2013, 02:26 PM

Sheet named as " number -- prefix".
And I'm not sure what you mean by next question..
I thought which columns we need to pull.
Thanks,
Safi.

**arlu1201** · 04-12-2013, 02:28 PM

No, i asked you the sheet name where the paths will be given and also the column where the paths will be listed.

**safi0102** · 04-12-2013, 02:33 PM

Sorry arlu..
The paths are not in any sheets..
If u wanting me to put the paths in sheet.
I'll prepare it for you now..
T
TIA,
Safi

**arlu1201** · 04-12-2013, 02:52 PM

If the paths are not listed, then what was your initial plan to work it out?

**safi0102** · 04-12-2013, 02:56 PM

Hope i have misswrote something..

is there any possibilities to add the path's inside the codings ?

Daily i have to check these folders and need to copy the data and pasting into a single sheet.

Thanks,
Safi

**arlu1201** · 04-12-2013, 03:02 PM

Are the paths likely to change frequently or is it the same paths that you need to access each time you run the macro?

You can do this -

Have an excel sheet which contains the paths. The macro will be in this excel sheet and it will consolidate the data for you in the 2nd tab of the same file.

**safi0102** · 04-12-2013, 03:10 PM

No path will be same..
So can you please provide me the macro file ?

many thanks,
Safi.

**arlu1201** · 04-12-2013, 03:22 PM

No, what i meant to ask you is - If this week you run the macro and you have 10 different folders, next week when you run the macro again, will you use the same 10 folders or there will be some others?

**safi0102** · 04-12-2013, 03:24 PM

No, The same 10 folders will use run. Folder willnot change.

thanks,
Safi.

**safi0102** · 04-13-2013, 04:58 AM

Hi Arlu,

I have attached the worrkbook which contains path's..

Can you please help me ?

Thanks,
Safi.

**nitesh_greatie** · 04-13-2013, 05:28 PM

Hi,

I am also looking for some code to do same kind of work

**mconnelly2012** · 04-13-2013, 06:19 PM

Wouldnt you be able to connect the files together as source files, copy the data to different worksheets respectively, and set it to auto update, and create an 11th tab with the formulas you need to interperet the 10 tabs?

Have workbook 1-10 as sources and assign them to copy the worksheet you need to a worksheet in your destinaation. The source files would need to be locked into there locations i do believe.

**safi0102** · 04-15-2013, 03:56 AM

Good Afternoon Arlu,

Did you managed to have a look on my post ?

Thanks,
Safi.

**arlu1201** · 04-15-2013, 04:11 AM

Did you review post 22 and see if the suggestion works for you?

**safi0102** · 04-15-2013, 04:53 AM

HI arlu,

Yes, But thing is that each and every time i have to open all the 10 sheets and have to copy the deails right ?

It's same i'm doing in a single sheet.

Thanks,
Safi.

**arlu1201** · 04-15-2013, 05:40 AM

Ok, let me check what solution i can work out.

**safi0102** · 04-15-2013, 06:17 AM

Yeah okay Thanks Arlu..!!

I'm looking forward to hear from you for a better solution..!!

Thanks,
Safi.

**safi0102** · 04-15-2013, 02:59 PM

Hi Arlu,

The attached file i ahve found in te internet seems taht thsi is doing the same what i need, But we need to do some amendments in the coding..

can you please have a look at it ?

I'm not sure whether i can post the Thread link here ?

So just i'm attaching teh file.

Please try to run it and help me to sort thsi up ?

Thansk for all your help.

Safi.

**arlu1201** · 04-16-2013, 07:39 AM

Can you please unzip the file and load it here? I have an issue with winzip on my system.

**safi0102** · 04-16-2013, 07:58 AM

Arlu,

Here is the master file with the Macro, Please look at it.

Seems taht the coding is wrote for combining teh multiple workbooks.

We have to choose the workbooks in a sinlgefolder, each and evry time we run the macro.

Thanks,
Safi.

**arlu1201** · 04-16-2013, 08:13 AM

So all the files in each folder path should be consolidated right?

And all the files have just one sheet? All the columns from the sheet should be consolidated?

**safi0102** · 04-16-2013, 08:30 AM

Yes, All the files in each folder path should be consolidated..!

The workbook may contain more than one sheet but all workbook must have a sheet named as " Number -- Prefix "

And not all the cloumns required, From A toZ will be okay..

Thanks,
Safi.

**safi0102** · 04-17-2013, 03:57 AM

Experts,

Can you please help me ??

Thanks,
Safi.

**MadhuMithaP** · 04-17-2013, 04:42 AM

Please let me know the code for Macros.How to copy 1 row from workbook1 and paste it in another column in workbook2

**arlu1201** · 04-17-2013, 05:24 AM

MadhuMithaP,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**arlu1201** · 04-17-2013, 06:22 AM

You have mentioned the paths as - T:\GBCWBRKCS001\NortelSW\GNP_NGNP THUS_YOURCOMMS\BT\GNP\ENERGIS\POI\ABERDEEN (IMPORT)\EXPORT\PDI\CURRENT\MASTER

So is Master the last folder and the files reside inside this folder?

**safi0102** · 04-17-2013, 06:33 AM

Hi Alru,

Thansk for looking in to it..

Yes, Master is the folder and it contain a Single workbook.

Thanks,
Safi.

**arlu1201** · 04-17-2013, 06:35 AM

The folders can have any number of files in it right?

**arlu1201** · 04-17-2013, 06:43 AM

Try this code

Please Login or Register  to view this content.

**safi0102** · 04-17-2013, 06:51 AM

Arlu,
No, Most of all the Master folder should have only i file in it.

I ahve tried taht, And recived an error msg taht "Run Time Error - 1004"

Thanks,
Safi..

**arlu1201** · 04-17-2013, 06:54 AM

On which row are you receiving the error?

**safi0102** · 04-17-2013, 07:04 AM

Arlu,

No Lines were highlighted in teh coding

**arlu1201** · 04-17-2013, 07:05 AM

When you get the error and click on Debug, it will take you to the code window and highlight a row in yellow. Which is this row?

**safi0102** · 04-17-2013, 07:07 AM

The pop up doesn't asking to debug..!!

Have two option " OK " and " HELP "

Thanks,
Safi.

**arlu1201** · 04-17-2013, 07:22 AM

And if you click on OK, it just stops?

Let me test it at my end.

**safi0102** · 04-17-2013, 07:24 AM

Yes, It's just stopped running

Yes, Please

Thanx,
Safi.

**arlu1201** · 04-17-2013, 07:34 AM

Try this code

Please Login or Register  to view this content.

**safi0102** · 04-17-2013, 07:42 AM

Arlu,

Can you please advise me taht where i want to put the Path ??

Thanks,
Safi.

**arlu1201** · 04-17-2013, 07:47 AM

You need to put the code in the file containing the paths. The macro will automatically pick up the paths from the 1st sheet and consolidate the data in the 2nd sheet.

**safi0102** · 04-17-2013, 07:47 AM

I have pasted teh Path's in the same sheet in the Range of A.

Still i'm getting the Same error

Another error : 400 , No wordings

Thanx,
Safi

**arlu1201** · 04-17-2013, 07:50 AM

You are pasting the paths in column A right?

I ran it at my end and it worked fine. Check if the file paths are correct which you have listed. I had changed it to my system paths while doing the testing.

**safi0102** · 04-17-2013, 07:59 AM

Yes, I have teh path's in Column A.

But still im getting the eror no : 400..!!

Thanx,
Safi.

**safi0102** · 04-17-2013, 08:10 AM

Arlu, found the bug ..!! The first file has saved in "xml" format..
Now the code is working fine but it's just pulling only 2 rows from each workbook.

**arlu1201** · 04-17-2013, 09:36 AM

Change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**safi0102** · 04-17-2013, 12:33 PM

That's wonderfull..!!!

The code work's fine

Really it's much appreciated and What a star youare

!!

Cheers,
Safi.

Combining multiple workbooks from different location in to one sheet.

LinkBack

Thread Tools

Rate This Thread

Display

Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

How to use macros to copy a row from worksheet1 and paste it column-wise in worksheet2?

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Re: Combining multiple workbooks from different location in to one sheet.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions