Opening Workbooks in a folder

I found this code and modified it to do somewhat what I want. Here is the problem. when it copies the formula from the workbook that is open it uses "wbResults" in the formula. I want it to use the workbooks name in the formula so the formula works? Also I need to place something in the code that lets me pick the active cell so the script can keep running. Here what I have so far maybe someone can take a look and give some advice.

Please Login or Register  to view this content.

Thanks So Much for any help!!! , Mike

Hello Mike,

Here is the code to insert the open workbook name into the formula...

Please Login or Register  to view this content.

Where will the ActiveCell start and what are the other cell addresses that will have their formulae updated? For example, they way the code is now if you start at "A1" it will change the formuala in "B1", "C1", "D1", "E1", and "F1". Where are you starting in ThisWorkbook?

Sincerely,
Leith Ross

Good Day Leith,

What can I say I was so close this time. However Your Modification Worked Great. Here are some additonal things I need from the script if possible.

I want the script to start at cell J4. However as you can see with my offsets I then need the script to move to the right one space. once it finishes the current row it moves down to the next row with offset (1,6) which that part is working. I also need the script to know when it finishes with cell p24. At that point the script will need to move to the next worksheet to the right. in this case it would be moving from the Jan sheet to the Feb sheet. and so on till it reaches the P24 Dec sheet.

So sum up the script needs to start at J4 and end at p24 on everysheet Jan through Dec (wbThis).


Any additional help would be great.

Thanks Again Leith your work here is outstanding!!!! I could never thank you enough!!!!

Mike

Hello Mike,

In your code you have Offset(1, 6) which says you are going down 1 row and 6 columns across. If you start at J4 then the next cell would be P5. Did you mean to start at J4 and include cells K4:P4? Just want to be sure I understand this correctly.

Sincerely,
Leith Ross

I have also been working on this a bit trying to get somethings to work so here is what I have so far. However it runs just wont work like I want it to.

Please Login or Register  to view this content.

Hello Mike,

I changed the macro based on what you have posted. Try it and let me know if I got it right.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi Leith,

First off Thanks for your help.

The script appears not to be working as well now. It looks as if it is not putting the formula's in the cells except the first range (J4:P4). However I think I understand why. and that is my fault because I didn't say that cell J4:J8 are merged cells meaning J4 is really cells (J4:J8) So really it looks like this:
J4=(J4:J8) K4=(K4:K8) and so on. It didn't seem to be a problem till we changed the formula to cells. I will include a example workbook so you can see what I am speaking about. Also I didn't run the script long enough either to see if it switchs over to the next sheet.

Key to examples:
wbThis = Month Sheet
wbResults= Week Sheet

WbThis has 13 sheets the first sheet is a summery of all the next 12 sheets. I have only included the Jan sheet which is the 2nd sheet in line. the rest of the months follow this sheet.

wbResults is the sheet I'm pulling the data from all 59 sheets are exactly the same and only pulling the data from c3:I6 then the script closes and opens the next sheet.


Let me know if you need more information. And thanks again.

Mike

Hello Mike,

Sorry about the late question. Everything seems to happen at once. Do you want to transfer all the data from each of the monthly sheets in workbook to the "Large Area" workbook or do you want to something else? Just so I am clear on this, each row in the "Large Area" worksheet will have the formula linking it to the sheet in the "This Month" workbook, correct?

Sincerely,
Leith Ross

No Problem I have been working myself off and on all day.

Lets see let me try to simplify this mess...lol

there are 59 workbooks with one sheet each in the large area folder all I want is to open up each of those workbooks(wbresults) one at a time and take the information from row 3 col c,d,f,g,h,i Then paste the link to the (wbThis) Jan -Dec in the respective week slot

wbThis has 13 worksheets in this order 2009 totals, Jan,Feb,March,April,May,June,July,Aug,Sept,Oct,Nov,Dec

So basiclly I want the infomation from the week sheet on the monthly sheet.

I think I confusing the issue myself.

Let me know if you need additional info...

Thank You, Mike

Good Morning,
I have been working on this script and with the help of some small posts and research I have almost got this working. I think I am only missing one piece of the puzzle now.

What I need now is to only check and see if there is a date in column(B) before going on with the calculation in the (J) Column.

Any Idea's
Thank You, Mike

Here is the updated code

Please Login or Register  to view this content.

Well I got it to work, Although it may not be a perfect script layout. It works... Many thanks to everyone for there input and special thanks to Leith Ross.

Thanks Again!!!!

Mike

Please Login or Register  to view this content.