producing multiple tabs

thanks that works well but that its going to delete all my holiday files I have on each worksheet but excellent anyway......just to be a bit more of a pain is there a way to date every sheet like that as well so say cell A1 is Jan 01 sheet 2 cell A1 Jan 02......sorry just posted this up and didnt see the next reply i will check it out and reply thanks guys

ok that last one puts the dates in but they all say the 10th ie jan 10 feb 10 how can i alter this

no worries i worked it out great replies guys helped me out big time

thanks again but 1 problem i had was that when i tried on a new workbook with junk in a few sheets it deleted what i wrote i think it was because i asked for new tabs my mistake is there a way of renaming tabs and keeping the date in a certain cell so i dont lose my info on any of the data i have on my sheets ...sorry for confusing matters

Ok on this dummy sheet i have writtrn some mumbo jumbo if i run the macro i will lose this info by doing that on my holiday one i will lose sall my info if i havnt explain i will send through a blank sheet when i return to work in a few days if thats ok it will probably show you more what i mean thankyou

As Buran states neither code deletes your original data

Is the problem that you need to add to your workbook?
and
Not as we both assumed create a new workbook.

guys i appreciate you both looking at this what i will do is send through my actual form when i return to work and see if that clears it up a bit better i will first of all try and see if it works and let you no thank you anyway

Guys ,
as you can see from this workbook what i was looking for was to rename the tabes as Jan 01 etc and cell B2 as the same date, without losing the information on the sheets i have only set up a few tabs on this work book,the macro that you provided made new worksheets which loses all my information hope this makes it a bit clearer
thankyou

sorry guys never worked have left a screen shot for you i think im getting to the stage of manually
renaming everything lol

Where the line ws name == format is thats the error coming up

Please Login or Register  to view this content.

Check in the workbook you are trying

How have you formatted cell B2

I ask because in your original sample.

You have formatted cell B2 as mmm-yy that gave Jan-01 by entering 01/01/2001
after copying the the next sheet and changing Jan-01 to Jan-02 did you notice the formula bar returned 01/01/2002
.....and so on Jan-03 > 01/01/2003............ Jan-04 > 01/01/2004

All these cells will therefore return Jan01 when formatted mmm-dd

Could this be where the problem lies?

i can see that yes i did format it like you said but im losing hope here i just wanted to show the dates as in B2 as Jan-01 and the tab as Jan-01-2010 i just dont get it my full workbook wont upload says its invalid i really am at lost now thanks for your help anyway i thing i will just slog it out and individually rename it all

Copy the first 6 sheets to a new workbook and send the new book.
I'll wait for it.
Don't despair - it will be something simple that is causing the problem.

thanks for your patience heres 6 sheets as requested

Hi daznav

The last code Buran posted ran first time - no problems.

The only thing I noticed is that all your sheets have Friday in cell F2 is that correct?

Try saving the book you want to convert using saveas
remove all marcros from the copy and send me that copy.

There has to be a reason behind this

Again I'll wait up for your reply

I should have changed that aswell sorry just getting late now i tried Buran last one and it worked so i will try in the morning on my saved copy one thanks for waiting up and having a go at this i will let you know how i get on

Hi Marcol,
bit refreshed now......problem with my workbook is that when i zip it it uploads as an invalid file cant understand why and also unzipped

Ok marcol i have the zip file.
now from here i want to keep all the data on the sheets ok.....the things that need changing are as follows

All tabs to read dd-mmm-yy

B2 to also read dd-mmm-yy

F2 to have the correct day

now it dosent matter really what format the date is but i think it looks better in this format if you think something else is better i go along with your expert judgement

hopefully i havnt missed nothing out

Sounds like your file is corrupted

Try saving the file you want using SaveAs remove all macros from the saved copy and try sending the new file.

If that dosn't work let me know

However

We could produce a template file to produce what you want, the scenario would be

1/. In the template file there would only be one sheet with all the layout as you have it.
2/. All you would have to do is fill in the names and any other data you need in this first sheet only.
3/. Then run the macro, it will produce all the sheets you need in a new workbook and retain the template complete with data you have entered for future use.
4/. All you would have to do is check and amend this sheet before running the macro again for another period

Keep your chin up - we'll get there !!!!!

actually the one sheet sounds good lol im sure you will crack this

Hi

All 120 sheets in your workbook have the same date in B2...... - thats why some of the solutions offered by myself and Buran would not run for you!

You asked for the sheetnames to be based on B2, we assumed they would be different.
Not an unfair assumption !!!!!!........

I will give you a solution for both sitations although the single sheet option is the real solution to your problem.

Can't do it right away - give me a few hours.

sorry about that i copied all sheets from sheet 1 only way i could zip it if i was to rename manually it would defeat the object no worries im off to work anyway now, so any how you think this will work i will go along with you both im all ears guys

Try this out.

This routine is as I described in Post #34

Fill in whatever data in sheet "Format" no need to fill in date and day (B2 F2) leave them blank
change anything you need to on this sheet.

Just don't put anything in B2 & F2 and don't rename this tab.

This programme will produce a workbook with sheets between any dates you choose.

Let me know what you think.

do i put the dates in the macro if so where and what format

Marcol i have just had a go and that is exactly what i was looking for absolutley brilliant, i hope belgium win all the world cups going lol,that is really good mate thanks alot