Macro creates new sheet but when running it errors.

Please Login or Register  to view this content.

Try this instead, I don't understand what you are talking about with the control number.

Please Login or Register  to view this content.

Where does it go? Do you just have one number in T7 which you overwrite or do you want a list of them? Does it reset when the date changes?

There will be 2013MT1 in T7, when the macro is run to create a new worksheet it needs to change to 2013MT2 when run again it needs to change to 2013MT3. We are trying to keep these by year, Material Transfer, Number 1, 2, 3 and so on then in 2014 it will start back at 1.

Yudlugar, I REALLY appreciate your help.

Ok, so something like:

Please Login or Register  to view this content.

Yeah, I think so, where do I add that in?

Depends - do you want the updated or the old value of 2013MT on the copied sheet. Eg. I have a sheet called "TestA" which has 2013MT6 in T7, should the sheet TestB have 2013MT6 or 2013MT7? should TestA keep 2013MT6 or change it to 2013MT7?

I'm assuming that TestA should keep 2013MT6 and TestB should have 2013MT7, which would be:

Please Login or Register  to view this content.

The original 2013MT6 needs to stay the same, when the new sheet is created it needs to be named 2013MT7 then it stays the same and when the next is created it needs to be named 2013MT8. It is a numbering sequence

I think the date format must be different on your system, I'd say you need to change Strings.Mid(Now(), 7, 4) which was based on the date being "06/06/2013" to strings.mid(now(),5,4) which presumably means your system date is "06062013".

I think that is what I did regarding the numbering sequence but I wasn't sure how your sheet works fully - what happens if someone presses the button on a sheet that isn't the most recent in the numbering sequence?

I have that cell set as text would that matter? I dd that so I could put the MT in there. Let me change it and see what happens but I didn't want the whole date in T7 just the year.

I know, the mid function extracts part of a string. So the now function will return the date as "06/06/2013" on my machine. Therefore mid(now(),7,4) will take 4 characters starting at the 7th, which is the 2013 part. Another option would be that your date is "06/06/13" which means you would need "20" & mid(now(),7,2) to get the year.

what happens if you use Range("T7") = now()?

I will try it but, T7 has nothing at all to do with a date other than the year. I need the year(2013)MT1 meaning in year 2013 this is material transfer 1, there should be no date tied to it, this is just a naming scheme like a invoice number.

I know, I am using the now() function to get the date, and then extracting the year from it. As far as I know there is no equivalent year() function I Could use. Otherwise, how will you know what year to put in T7?

If you could tell me what happens in T7 when you put Range("T7") = now() then I can change the code to cope with whatever the format you get is. When I tested it on my machine it worked as per your requirement.

Same thing 13 1MT1 is what it returns in T7 when the macro is run. Is there a way to make the 2013MT static and just add 1 when the macro is run, then in 2014 I can just go change it to 2014.

That isn't right. Run this macro:

Please Login or Register  to view this content.

What is in T7 after this?

If you want to make it static just put
Range("T7") = strings.left(Range("T7"),6) & strings.right(Range("T7"),len(Range("T7"))-6)+1

(T7) 6/6/2013 10:11 is what I get when I run the following macro

sub macro_1()
Range("T7") = now()
end sub

Hi

This is probably easier to do in Excel iteself rather than VB, just put these formulea into t6 thru t8 and increment t8 by 1 each time you run the macro - Range("T8") = Range("T8")+1



T
6 =TEXT(TODAY(),"dd mmmm yy ") & T7
7 =CHAR(64+T8)
8 3

Originally Posted by GGriggs

Hi

This is probably easier to do in Excel iteself rather than VB, just put these formulea into t6 thru t8 and increment t8 by 1 each time you run the macro - Range("T8") = Range("T8")+1

T
6 =TEXT(TODAY(),"dd mmmm yy ") & T7
7 =CHAR(64+T8)
8 3

I cant use T6, that date needs to be entered manually for the rest of the code to work and T8 needs to be blank. I just need that cell to say 2013MT0 and every time the macro is run add.

Example 2013MT0, run macro to create new sheet 2013MT1, run macro to create new sheet 2013MT2

Ah, that makes it a little more difficult as the length of your now() result will change as you move from 6/6/2013 to 12/6/2013 to 12/12/2013. Try this one:

Please Login or Register  to view this content.

Holy Crap, you are the man!!!! Thank you SO freaking much!!!

No problem, got there in the end...

Wait one more thing, I need when it created the new worksheet to clear all the data except the control number

I've kept the sheet name bit in T6 as well. I can't see how this would work otherwise.

Please Login or Register  to view this content.

Wait I didn't explain correctly, wow did I mess that up. That cleared the whole sheet. That was wrong, LOL Let me see how I can explain this I need A13 - W25 cleared. Hell the easiest thing would be me make a snapshot. I will do that and PM you a link.

Please Login or Register  to view this content.

The one above gives a compile error on this Sheet_Exists:

Sorry, it didn't copy all of the code from the module. Add the stuff after sheet_exists from one of the previous iterations.

Well the scope of this has changed. Due to other people using this it needs to be made more simple (although it is already). The Original sheet needs to be a "Master" and when the macro is run and new sheet is created the macro button needs to go away so sheets can only be created off of the one. The sheet names and all the rest need to stay the same. Is there a way to make sure the date field has a date in it, if not I need a popup that says you must enter the current date. I hate to ask you to do this after all the work yesterday.

When you put the code can you also explain some of what it does so I can start learning more of this?

To be honest, I think that you need to take a step back and thoroughly plan out how this workbook is going to function before you jump into the code. These issues where ones that I mentioned whilst working on this yesterday. This thread has also moved a long way from what the first post/thread title was about. I would suggest that you think about what you need the workbook to do, find the parts that you have a problem with and then post a new thread with those problems. Make sure the title of the thread is descriptive of the problem!

For what it's worth. If I were to do this, I would move away from using buttons on regenerative sheets as it makes things a bit messy. I would approach this workbook by:
1. Create a hidden sheet which has the format that you need for the sheets that are to be copied (i.e. with the data you don't want copied removed)
2. Create a custom menu or toolbar button to generate the new sheets as neccessary (see: support.microsoft.com/kb/830502)
3. Assign the menu or toolbar button to a macro to copy the hidden sheet and rename it with today's date (use the now() function)
4. Use the hidden sheet to keep track of what the new MT number needs to be.

Well I had it like I wanted it but when the boss looked at it he said I think because people in the field are going to be using it we need to simplify it more. I appreciate all your help. Well noted.