Hi,
I have about 40 workbooks and I want to automatically look through the last sheet of each workbook in the folder and pull out information for all REL duties and display the following in a new workbook:
Name
Date
RELnn (nn being the number)
I've attached an example sheet and highlighted in red the information i would expect the macro to find.
Is this possible for excel to do or is it too complicated?
Jimmy
Last edited by jimmy1981; 12-21-2010 at 04:20 AM.
Try this, edit the path string at the top to point to the correct directory.
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi JB thanks for replying,
This is working to some extent, but in some cases it's not returning the data and in some cases the date is the wrong way around (08/12/10 is returned as 12/08/10).
I've attached a book showing the 2 sheets it pulled the info from and the results so you can see what I mean. I've run it a few times and had the same results.
OK, I’ve had a mess around with it and changed
to .Value and that is giving me the correct date format.Please Login or Register to view this content.
And I now see that because a RELH duty is 1 cell lower than a RELF duty when it counts 6 down it goes past the date.
What I can’t figure is how to get it to count 5 for a RELH duty and 6 for a RELF duty.
Please Login or Register to view this content.
Last edited by snb; 12-03-2010 at 10:48 AM.
Maybe a little test:Originally Posted by jimmy1981
Please Login or Register to view this content.
JB, Perfect. Cheers mate.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Hi,
I know I marked this as solved but I'm after a little tweak.
The code supplied checks the very last sheet in every workbook opened, what I'd like it to do is gather the sheet name from cell G3 and pull the information from the sheet if the sheet name equals G3.
So, say the workbook has 5 sheets numbered 1,2,3,4 & 5.
The cell G3 (in my master book) has the value 4
So pull the info from sheet 4.
Assuming G3 is on the report sheet, a small change:
Please Login or Register to view this content.
Thanks Beau, that works perfectly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks