PDA

View Full Version : Updating multi worksheets from closed workbook



Vikki_61
12-08-2007, 09:04 PM
Hi VBA gurus

I know this is a big ask ? but I certainly do not have the skill :banghead: to do what is below, so I hope some VBA wizard out there can help. :help

On Event open workbook (wookbook A)

For worksheets 3 though to worksheet 9 and only columns D, E, F, G, H , I and J, and only rows 4 through to 35 auto update from a source workbook

Note 1: - as you will see in the attached demo test file ?Workbook A? the row range 4 to 35 varies on each worksheet as the number of towns columns varies on each worksheet. So on one worksheet it might be row 4 to row 12 requiring update whereas on another worksheet it could be row 4 to up to row 35.

Note2: ?the source workbook is a closed /non active workbook on another server in my network ?path is X/sourcefiles/weeklydate/countfile.xls. In the source workbook the source is sheet 1.

Only update when there is a match of string of the place location name which are ALL listed in column C of the source worksheet ?matched to that of string place location names in workbook A in their respective worksheets 3 through to worksheet 9. Then when there is a match ? copy the content of the cells that correspond to the place location names in the source workbook (countfile.xls) from the columns D, E, F, G, H, I, and J.

Note 3. The way in which the source countfile works is that each week it will be updated with values/figures starting with column D in week 1 and column E in week 2 and column F in the third week and so on?..In the first week only column D will have values/ figures and the other columns will be empty ? and second week only columns D and E will have values/figures ? to make the code easy it would be OK to copy across the other columns when they are empty ? thus leaving the other columns in workbookA also empty. So in week two when workbook A is opened and is updated from the source file with values/figures for workbook A?s columns D and E of worksheets 3 through to 9 the other columns F, G H, I and J on those worksheets would have only be empty cells or just not updated at all ?as the source file at that time would have had only two weeks of figures entered.

Also copy across the column headings in row 4 for columns D, E, F, G, H, I , and J as those headings may change as they are dates.

The final bit that is important is that as worksheet A is updated the formula in column K it will need to auto update as well as it is the initial figures in column D minus the figures in the most recent column updated ? so if column G is updated today the formula needs to be =D5 ? G5 and =D6-G6 and so on whereas when updated next week the formula would need to change to =D5 ? H5 and =C6-H6 and so on.

Hope this is not too hard to follow and feel free to ask me any questions. :think:

My thanks in advance:hi:

Vikki_61:cloud9:

Simon Lloyd
12-08-2007, 10:19 PM
Cross posted http://www.ozgrid.com/forum/showthread.php?p=403119&posted=1#post403119 username Tony Mos

Vikki_61
12-08-2007, 10:57 PM
Yes Simon one of my work colleagues is a long term member of OZgrid and offered to post it there in that forum - I am only a new member of Vbaexpress -so can you tell me if that is Ok or not...and why would it matter

Simon I still need help with this so if you can suggest where I can get started with it would be appreciated....I know how to copy a range from one workbook to another both that I want to do here is more complicated than that.

regards

Vikki :hi:

rbrhodes
12-09-2007, 10:34 AM
Hi Vikki,

Cross posting. Here's an explanation from Oz:



"Do not cross-post. This is when you post the same question on other forums, newsgroups etc. This will be wasting the time of the kind volunteers as they could well be trying solve a problem that has been solved elsewhere. This is like ringing 5 different cab companies, jumping (http://www.vbaexpress.com/forum/) in the first that shows and not caring less about the other 4. If you have cross-posted, please at least supply the URL to the other post(s)


In other words I (and perhaps others) work on your question here, another group works on it at Oz and _someone's_ time is totally wasted as the collaboration is non existent.


Even if you post notice that the question is posted somewhere else (the absolute least you should do!) it's still difficult to follow more than one thread in more than one location.

I'll have a look at your sheet for you...

Cheers,

dr

Aussiebear
12-09-2007, 12:14 PM
Cross posted http://www.ozgrid.com/forum/showthread.php?p=403119&posted=1#post403119 username Tony Mos

That might also explain why some of the macro's in the initial workbook had been written by "Tony" as well.

Simon Lloyd
12-09-2007, 02:03 PM
That might also explain why some of the macro's in the initial workbook had been written by "Tony" as well.They were ALL recorded by Tony

Vikki_61
12-09-2007, 05:55 PM
Thanks rdrhodes if you are able to offer some help that would be great.

Simon....yes of course Tony did do the macro as he did most of the work on this for me - he works about 500km south of me and I had emailed him my requirement specs and he emailed me back the speadsheet with what he could do . I then asked him to submit my question to OZ grid and I guess he did without thinking about it . He just wanted to help me and now I am most upset that he has been struck off Ozgrid because he helped me and he has been offered no Natural justice process to ask OZgrid to review his case...our employer had offered to reimbust Tony and I for doing an online Ozgrid training package in the new year mainly at Tony's continous demonstration to our boss that VBA is so useful....I can tell you we are now reviewing that.....wish you would have had the refinement to have asked me first....anyway Simon hope you enjoy your Xmas ....:motz2: Hope you have a wife who can chew your ears off

If anyone else on Vbaexpress can help me than that would be appreciated

Vikki

Simon Lloyd
12-09-2007, 06:19 PM
Vikki all forums have some rules and they are there to help both posters and helpers, Tony is not struck off indefinite he just has to post a PM to the administrators and agree to follow the rules he agreed to in the first place being a member of Ozgrid he will have been only too aware of the consequences of not posting a link to the cross post. My posting the crosspost links was nothing more than to allow collaboration amongst helpers Please read this (http://www.excelguru.ca/node/7) it explains about cross posting!, whilst crossposting isn't wrong it would be curteous to say you have and post a link.


Hope you have a wife who can chew your ears off

she also had misconceptions as to my motives for helping others and is now the ex!

Vikki_61
12-09-2007, 06:46 PM
Simon

Sorry that your Ex didnt understand your helping others -as I know in my heart and mind the world is blessed by those who do help. And I am so bless already by what I have learnt from others on this forum who ahve taken time to help me.

Thanks for your explaination of cross posting -I can understand it is important to know that and to let people know if that has occurred. As for Tony and OZ grid he tells me that when he tried to log on it said he is struck Off and it says "never" to be reinstated. He is so upset about it. I will email him and tell him what you suggest about emailing the administrators.

I was a bit cross with you -but now I really do hope you enjoy your Christmas. :rotlaugh:

Thanks from the land down under

Vikki :cloud9:

rbrhodes
12-09-2007, 07:28 PM
Hi Vikki,

Here's a Workbook open sub that will update your sheets. It takes approx 13 seconds to open the workbook on my machine tho.

Part of the reason it takes so long is that it looks at every placename on each sheet and performs a 'Find' in the source sheet for that name then copies the row...

If your data on your source sheet is in the same order and is also the same number of rows it could copy 'chunks' of data and therefore be faster.

Another suggestion is to adapt it to run from the Source file (perhaps on BeforeSave) so that the Destination file is updated ONLY when the source is updated/changed - that would be way more efficient, I think.

Anyway here's a sample that does what you asked (I think)...

Vikki_61
12-09-2007, 08:53 PM
Thank you Mr rdrhodes

I am speachless just how well it works, your suggestions about speeding it up are good - however at this time I would not know were to begin with how to coude the source file to uplaod when it is updated -so any tips you have would be icing on the cake.

And thanks for the well thoughtout formula that updates the count when to column value change and you have handled the Div/o issue for me which was something I had on my list.

However something I would like very much to know how can I get column headings to slope on a 45% angle as you have done with my demo file ...I think it is very effective. Please share your wisdom :clap: :clap: :clap:

I don't know many Canadians but they must be pretty clever. :bow:

I do love these smilies :hi:

An Ozzie thank you :beerchug:

Vikki