Macro to update worksheets

[eoghanmolloy]

Does anyone know a macro I can use that will update sheet 1-4 from the information in sheet5? I have updated Sheet1 with what I need the macro to do from the data.

[jaslake]

Hi eoghanmolloy

Try this Code in a General Module

Please Login or Register  to view this content.

[eoghanmolloy]

Thanks for that...works perfectly

[eoghanmolloy]

I've had to make some changes with the data source and layout which will affect the code above. Please see attahced files. Can you let me know what the new code should be?

[jaslake]

Hi eoghanmolloy

It's a major rewrite. Before I start are there any more changes?

[eoghanmolloy]

Hi John,

Apologies, I persumed it wouldn't be too much of a hassle to change the coding if the data source was in a different spreadsheet. Thanks for taking your time to do this.

The only changes that I will need to make to the completed code is the file name of the spreadsheets and A9 of Test2 will not be called 'Code' and the same with B5 of Test1. There will also be about 90 worksheets that will need updating compared with the 4 example ones I have on Test1.

Will these changed have any major impact on the code apart from small editing changes?

Thanks,

Eoghan

[jaslake]

Hi Eoghan
What will be the Sheet Names in Test1? Will they be the SAME AS the Names in Column A of Test2?

[eoghanmolloy]

Hi John,

Yes, the Names in Column A of Test2 will have a Sheet named after it in Test1. I don't know if it will make a difference but the codes will not be AAA,BBB, etc - they will be 4 character codes mixtures of letters and numbers.

[jaslake]

Hi Eoghan
This does not matter as long as they're all spelled correctly and don't have invalid Characters

Please Login or Register  to view this content.

Let me play with it.

[jaslake]

Hi Eoghan

The code runs from Test1, Module1 and assumes both files are in the same directory. The code can be changed if the files are NOT in the same directory.

Modify these variables as required

Please Login or Register  to view this content.

Test2 can be open or closed...doesn't matter. Let me know of issues.

[eoghanmolloy]

Hi John,

I have updated the code as follows but a 'Complie Error: Variable not defined.' occurs. Can you let me know what I need to change? Thanks.

Please Login or Register  to view this content.

[jaslake]

Hi Eoghan

My best guess is these two lines of code...no variable is defined

Please Login or Register  to view this content.

[eoghanmolloy]

Hi John,

Do you know how I can change it so the macro works?

[jaslake]

Hi Eoghan

Is this not the Original code from Post #2 (as modified by you)? Simply use that code...or, tell me what you're trying to do.

This is NOT the code from Post #10 which assumes two workbooks.

[eoghanmolloy]

Apologies John I completely missinterpreted what you said in message 10! For some reason I did not see the two amended spreadsheets you had uploaded. Problem solved! Thanks!

[jaslake]

Hi Eoghan

No apology necessary...glad you got it sorted.

[eoghanmolloy]

Hi John,

It works fine on the example I posted but when I try to use it on my spreadsheet a "Run-time error '9': Subscript out of range" error appears. I don't know if this is beacuse with the spreadsheet I'm using this macro on has about 90 worksheets? Any ideas/suggestions?

[jaslake]

Hi Eoghan

What line of code is throwing the error? The message implies that the code is looking for something and can't find it. There needs to be a worksheet in Test1 for EVERY name in worksheet Data of test2 AND they need to be spelled the same way.

[eoghanmolloy]

The error was on the code below

Please Login or Register  to view this content.

This may be a bit of a problem then because the code did not have a worksheet on the Test1 spreadsheet. Is there a way to get round this?

[jaslake]

Hi Eoghan

I suspected that was the issue...let me look at it.

[jaslake]

Hi Eoghan

See if this works for you...replace all the code with this

Please Login or Register  to view this content.

[jaslake]

If you don't want the message let me know.

[eoghanmolloy]

Hi John,

I tried to run this macro in the spreadsheet im using and after about 15 minutes of waiting it crashed. Any ideas of what to do?

[jaslake]

Hi Eoghan

Perhaps break the File down to smaller segments, say from 90 worksheets to 30 or 45? Beyond that...don't know.

[eoghanmolloy]

I don't think I can go down the route of breaking the file down. Should I mark this thread as solved and re-post my problem again?

[jaslake]

Hi Eoghan

I don't know what happens when you say

after about 15 minutes of waiting it crashed

Do you get an Error Message? Does Excel close? Was Excel not responding? Did you hit the Escape Key to see where the code was in the Procedure? All of these are clues.

Try the Escape Key when you feel things are not happening...doing that is setting a Break Point in the Code...see if it's still running...let me know. I'd GUESS (only a GUESS) that the File is so large that the Process is going to take a bit.

Get back to me.

[eoghanmolloy]

Hi John,

Aplogies I thought I had replied to you - evidently not. After about 10/15 minutes of the macro running Excel is not responding. I think it's because the file is too big and will only get bigger. Is there a different way of writing a macro to solve this problem?

[jaslake]

Hi Eoghan

Not that I'm aware. You may wish to start a new Thread and describe the issue. Perhaps another can help you resolve it.