Loop sheets in more than one workbook

**nigelog** · 01-28-2009, 10:47 AM

Hi
I have a small macro that searches the sheets in a workbook and sends the info (if qualifies) to a new workbook before saving that workbook using a name date time format for records.

I woud like this macro to be able to repeat action in 8 more selected workbooks in a folder.
Question - can I name the workbooks I want to search - and - can I search all 9 workbooks before the data sheet saves and names itself, limiting access.

Code and sample attached

Please Login or Register  to view this content.

Any assistance appreciated. Rgds Nigel

**ravishankar** · 01-28-2009, 12:44 PM

Hi
place the 8 files in a folder along with nigel.xls (attached ) and run the macro. It will list 8 files in col A, opens each, runs macro2, saves them,closes it and next file opens
Ravi

**nigelog** · 01-28-2009, 01:01 PM

Originally Posted by ravishankar

Hi
place the 8 files in a folder along with nigel.xls (attached ) and run the macro. It will list 8 files in col A, opens each, runs macro2, saves them,closes it and next file opens
Ravi

Hi Ravishankar

I will have a look at this. If after running the macro I can then return the files to their original folder (there is no chance I can permanently move them or delete surrounding files)and specify the order they are opened then it will be what I am looking for.

Let you know

**nigelog** · 01-28-2009, 01:20 PM

Sorry Rav

first part of macro collected the filenames and listed them in a as stated. Macro then called file Chipliner - OK

but before collecting data as per Macro(2) it copied whole of workbook Chipliner to Fault Report 27012009 5:12 TR136 (dont know where TR136 came from it is the last sheet in the chipliner workbook)

It also had saved the workbook which I would have thought data from all selected files would need to have been colllected before save invoked. I have attached fault file created.

I will have a look to see what he cause is. Also the fault file, although not in the folder as directed to C:\, appears in the file list at the top.
File would'nt attach. Going to look at re ordering file list and removing fault file from top

**nigelog** · 01-29-2009, 06:21 AM

Here is the code uploaded by Ravashanker. It is going in the right direction and does collect the list of files in the folder.
However when the macro nigel() is run it goes through the first filemane in the list -opens - closes - but then it adds the fault file which is created by macro2() to A1 and "c:\" to B1.

It then rolls to next file in list "EJECTORS" but gives an error filename not found. I assume that it is now searching in C:\ instead of the folder.

Ravs code including my macro 2()
Attached file in which macro works in one workbook

Please Login or Register  to view this content.

There are only 8 workbooks in a folder containing 20 which are to be involved in this loop. If macro can thereby operate from a fixed list in seperate location it would be preferable.

I am struggling with this and any help appreciated.

**nigelog** · 01-29-2009, 10:06 AM

Try as I might I cannot get pass the error recorded when this line is highlighted(underlined in code).

Should the file nigel.xls be outside of the folder, and the folder targeted, in question before activating macro

Please Login or Register  to view this content.

**nigelog** · 01-29-2009, 12:29 PM

By changing the destination of my created faults file in macro 2 to the same folder as all of the source workbooks has now allowed the macro to continue looping through the workbooks in the folder.

However, as a result it is copying the entire contents of each workbook to the faults file and not the result of macro ran within the file.

I have searched for two days on this and other forums and I am surprised I cannot find a way to even stipulate a list a workbooks for the macro to work within.

I am going to drop Ravashankers solution entirely at this point unless I can find it can be adapted at a later stage.

At this stage I feel like just sticking a button in each workbook.

Any fresh input appreciated

**dcraker** · 01-29-2009, 02:20 PM

I tried the code that was provided by ravishankar and it works pretty good. Notice:

place the 8 files in a folder along with nigel.xls (attached ) and run the macro.

i believe the request here is to just only have the 8 files and plus nigel workbook in a folder by itself. This way, you always have an uptodate list shown.
What I see you could do? Here is something you could try, if you don't have a problem with names of the workbook not updating continuously.
I stepped through the macro to get all of the names of the workbooks into Column A, stopped the macro. Then I commented out the part (for safekeeping in the future) that will keep making the list:

Please Login or Register  to view this content.

Then I removed any unwanted files from the list generated, made sure to keep the cell A1 and B1 intact as well. Then stepped through the macro again to make sure it was opening the desired files. When I was satisfied with the results, I just ran the macro from there.

Of course, make sure you have a back up of any copy that you are doing so that you can at least backtrack. And if the file names do update and you don't keep the list current, then you will probably have a fail.
At least, that is what I would do.
Hope this helps

**nigelog** · 01-30-2009, 06:16 AM

Hi David
thanks for the response. Commenting out the section as you directed has indeed helped matters as the list was updating itself and including the destination file of macro2() and of course nigel.xls.

I have corrected this and can now loop through the appropriate files.

Problem now is the calling of macro2(). Macro2() now is copying the entirety of the open workbook to the destination file and not the "results" of the macro.

It also when it opens the second workbook overwrites the first workbooks results with the second in the destination file. It would need to add these results rather than overwrite so the destination workbook would have to remain open.

Macro2() when run in any of the 8 files correctly gives the sorted results so in itself is not at fault.

I attach the workbook nigel.xls and one of the workbooks in question.

Any help appreciated. Rgds Nigel

I have just noticed (but do not know how to correct) that macro2() when ran within Macroom*.xls works correctly when macro is invoked from within the file. If however the workbook nigel1.xls is open and the macro2() invoked from there, it gives the incorrect results.

**nigelog** · 02-02-2009, 05:19 AM

Bump - no answer as yet. Rgds Nigel

**Andy Pope** · 02-02-2009, 06:04 AM

Try this code,

In the file Nigel1.xls you need to change the contents of A1 so that it contains the path where the files are located.

Please Login or Register  to view this content.

**nigelog** · 02-02-2009, 10:43 AM

Hi Andy

mush appreciated. Code has worked a treat. I have implemented the code now into the appropriate workbook for launch and all works fine.

I will list as solved but I have 1 question. The target file is created and saved but it would be preferable if once the macro is activated that this saved fault file is left as the active workbook ready for the operator to print.

What section of code would I ammend for this to happen.

Again thanks to all who contributed to this answer.

Rgds Nigel

**Andy Pope** · 02-02-2009, 10:48 AM

If you want the final left open then comment out the Close method.

Please Login or Register  to view this content.

**nigelog** · 02-02-2009, 11:14 AM

Originally Posted by Andy Pope

If you want the final left open then comment out the Close method.

Please Login or Register  to view this content.

Perfect, many thanks. Rgds Nigel

Loop sheets in more than one workbook

LinkBack

Thread Tools

Rate This Thread

Display

Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Re: Loop sheets in more than one workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions