none
how to add date in worksheet name RRS feed

  • Question

  • Hi,

    I want to add date in the worksheet name. like MED 01/07/16 TO 15/07/16 (1) Where (1) will change (2), (3) likewise.

    Set wshT = Worksheets("MED " & Worksheets("P").Range("C2").Value & " To " & Worksheets("P").Range("D2").Value & " (" & I & ")")
    On Error GoTo 0
    If wshT Is Nothing Then
    Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    wshT.Name = "MED " & Worksheets("P").Range("C2").Value & " To " & Worksheets("P").Range("D2").Value & " (" & I & ")"

    Then I want to print it, like

     'If .Name Like ("MED & Worksheets("P").Range("C2").Value & " To " & Worksheets("P").Range("D2").Value(#)") Then
     If .Name Like ("MED " & P!C2.Value & " To " & P!D2.Value & " (#)") Then
     .Select
    DoEvents
    .PrintOut _
    PrintToFile:=True, _

    First case not working properly and second not at all.

    Friday, July 1, 2016 3:07 PM

Answers

  • Hi,

    There is one big worksheet with data. For printing purpose it gets split in to many sheets and get print out. now these lines working.      

    Set wshT = Worksheets("MEDI " & Worksheets("P").Range("C2").Text & " (" & I & ")")
        On Error GoTo 0
        If wshT Is Nothing Then
        Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))   
        wshT.Name = "MEDI " & Worksheets("P").Range("C2").Text & " (" & I & ")"

          If .Name Like "MEDI " & Worksheets("P").Range("C2").Text & " (#)" Then
          .Select
            DoEvents
            .PrintOut _
              PrintToFile:=True, _

    Actually I used "dd/mm/yy" as text format. And excel donot accept / in name of sheet. I changed - as separator, now working.

    Thanks

    Sunday, July 3, 2016 3:09 PM

All replies

  • Hi

    I made a sample file, it zip because Ms may remove the macro, I'm still using XL2003.

    It's not exactly what you want, it's a workaround. It may help you in any case.

    See link below.

    https://1drv.ms/u/s!AoqVU1ooQiw6gnqTlj2WL5AFbpEG

    To start, delete cell A1. If you need to start your numbers at 1 everyday, you will need to delete A1.


    Cimjet


    • Edited by Cimjet Saturday, July 2, 2016 4:53 PM
    Saturday, July 2, 2016 4:37 PM
  • Please elaborate some:

    1. Do you have several worksheets you want to rename to the base name plus a date and then a running number 1, 2, 3 etc?
    2. Do you, in the same workbook, have other sheets that you do not want to renumber or print?

    Best regards, George

    Sunday, July 3, 2016 11:12 AM
  • Hi,

    There is one big worksheet with data. For printing purpose it gets split in to many sheets and get print out. now these lines working.      

    Set wshT = Worksheets("MEDI " & Worksheets("P").Range("C2").Text & " (" & I & ")")
        On Error GoTo 0
        If wshT Is Nothing Then
        Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))   
        wshT.Name = "MEDI " & Worksheets("P").Range("C2").Text & " (" & I & ")"

          If .Name Like "MEDI " & Worksheets("P").Range("C2").Text & " (#)" Then
          .Select
            DoEvents
            .PrintOut _
              PrintToFile:=True, _

    Actually I used "dd/mm/yy" as text format. And excel donot accept / in name of sheet. I changed - as separator, now working.

    Thanks

    Sunday, July 3, 2016 3:09 PM
  • Thanks Cimjet ,

    So nice of you.

    Your sheet work fast. My code is slow. May be due to long procedure. Can you please check the code ?

    regards

    Sunday, July 3, 2016 3:12 PM
  • Have a look at the link below. Read the two answers given, it may help.

    From the information on this post, I can't see anything wrong but you're missing a lot of information. I'm still using XL2003 so not a good candidate to check for speed.

    http://answers.microsoft.com/en-us/office/forum/office_2016-customize/running-a-large-macro-from-excel-2002-in-excel/b5befb6f-e344-448a-842e-994a649e11bb

    If Your post is answered, would you mark it "Answered"

    Thanks


    Cimjet

    Sunday, July 3, 2016 4:28 PM
  • Hi drsantoshsinghrathore,

    its good to hear from you that you got a solution by yourself for your issue.

    Thanks for sharing the solution with our community.

    As your issue is already solved but you have a other issue with your code i.e. speed of execution.

    As you already know that the issue speed of execution is not related with the issue mentioned in the original post. both issues are different.

    so here I would recommend you to create a new thread for new issue instead of continuing in the same thread.

    when you create a new thread you can paste the link of the new thread here so that the member who are helping you to solve the issue can get the link of that thread and they will try to continue the discussion with you on that new thread. so that you will not lose the response from them.

    Thanks for your understanding.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 4, 2016 1:57 AM
    Moderator