Replies

I have my eggs in two baskets here. Not sure which way to go.

I would like to try Glens idea, but not sure I actually understand how to implement what he is suggesting. Ideally I would like to use the version I have been working with because I know it all works except for the speed of the macro.

I see your point with the way you have set it up and it has merit, however, some things dont seem to work now. For instance, when puting in a code for leave, this doesnt work now.

Also in the previous version any changes to the template were taken forward to the fortnightly sheets created in the macro. This was handy for me as if I had to make and changes to the sheets, this was carried through. For example – I had someone say today that the gray shading was coming out very dark on their printer. I changed the shading to pale green on the template and it carried forward. In the future I may want to make some formulae amendments to my the original file before issuing it and I dont want to have to make changes to each and every worksheet.

Hope this makes sense?

Hi Lagare

Bet you thought you were rid of me? I wonder if you can cast your mind back to this problem? I have returned to the original message so you can follow the thread.

I am still having a problem with this BEAST. The version you returned to me seemed to still run into 4 pages when I printed it, so I thought I would be clever and record a macro of the print set up. Then copy it into the module. This does work, however it takes FOREVER to execute. Are you able to assist me in speeding it up?

Kind Regards

Kerry

Hi Lagare

Bet you thought you were rid of me? I wonder if you can cast your mind back to this problem? I have returned to the original message so you can follow the thread.

I am still having a problem with this BEAST. The version you returned to me seemed to still run into 4 pages when I printed it, so I thought I would be clever and record a macro of the print set up. Then copy it into the module. This does work, however it takes FOREVER to execute. Are you able to assist me in speeding it up?

Kind Regards

Kerry

Thanks for this info – I will investigate this option in Word.

Thankyou Francois

Yep that worked.

What I have done is to slip in Sheets(“Template”).Visible = False at the the end of the routine. I can then get it back when I want to by using the format sheet unhide method.

This is exactly what I wanted to do.

Thanks team!

Can you give me a little more to go on?

I already have that line in the macro and I am not sure how to include it to acheive the desired effect.

Thanks

Thanks for all of your suggestions.

I need time to absorb this info and give it a go.

If I have no success I will post my spreadsheet for you to look at.

Thanks all.

Kerry

Woops – the Cell P27 is cell with a formula ( =P25+P26 ) causing a circular reference.

I have no idea how to get around this one.

Could the formula all be put in P27 instead?

Kerry

I just wanted to thank you for your help. I have the timesheet working now and although the Macro runs very slowly it does the job.

The best part of it all is that I actually learnt something. I feel much more confident about having a go myself and am inspired to do more with Excel.

Kind Regards
Kerry

I am almost too embarrased to ask, but here goes.

I have tried to nut this one out myself, but everything I tried didnt work.

When I looked at the Print Preview I found the copies created are in portrait orientation and the header does not copy over.

I almost hear the sigh – “oh what next??”

Kerry

Hi Bryan

Thankyou kindly for your explanation of the formula in your spreadsheet. It is obviously an option I could have considered.

You may see that Legare has adjusted the macro to fix this for me and it works very well. You may find it useful too.

Once again thank you. Isnt this site the best!! I have never come across such a helpful and friendly group. I hope I can offer someone help one day, instead of always asking the questions.

Kind Regards
Kerry

This is just what I was looking for Lagare. Thankyou very much. I am very excited about finalising this project that has been bugging me for ages. I will be free to work on other tasks on my long list!

I feel priviledged to have your help and I hope I am not stretching things too much by asking just another question?

Can you tell me why, when the sheets are copied the new ones are not formatted the same as the template. That is, they show some (not all) gridlines and the zoom is at 100%?

Also is it possible to protect each sheet in the Macro. If I leave the template protected before running the macro, the dates dont transpose correctly. So if I leave the Template unprotected, then so are all of the copies and they then need to be protected individually. I am reluctant to leave them unprotected as people always want to have a fiddle…

P15 on Master is carried forward to P26 On Template (example =Master!P15) – There after P26 on each sheet takes forward the total from P27 on the previous sheet. (example =’22 Feb – 7 Mar’!P27).

This is quite combersome, but I know no better way to do this.

Bryan – who sent the other response has an interesting formula on his spreadsheet, but unfortunately I am unable to interpret it.

As I said earlier, I am no whizz with Excel. I just know what I want, but not always how to do it.

Kerry

Thanks Bryan – I will take a look at your example.

Kerry