Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    And what about next year? Will you create a new file for 2006? Or should the name of the worksheet include the month and the year?

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    good note!
    Month and year...
    Similar DICEMBRE_2005, MARZO_2004... Etc..

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    See if the attached code does what you want (I can't test it)

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    Excellent code....
    But:
    1) error on this line (see img):
    Elenco.Range("A" + CStr(n)).Value = Caus

    2) Copy into every new sheet the first 2 line of IPSEMA sheet (are the title of record)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    2) Below the line

    Elenco.Name = strSheet

    insert this new line

    Worksheets("IPSEMA").Range("1:2").Copy Elenco.Range("1:2")

    This should also take care of problem 1)

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    I have understand the prob error 1004...
    when the macro make the new sheet not is possible to use the sheet IPSEMA such as a default sheet with all format cells, (i have see, is problem with the format of cells) and rename it with the famous index of variable DATA_DISK

    ahhh...
    But when e macro copy the first 2 line not is important to copy also the buttons...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    Do the following
    - Make a copy of IPSEMA, and name it Master.
    - Remove the buttons from Master, and clear the data (but not the headers and the formatting)
    - Hide the Master sheet.
    - Replace the lines

    Set Elenco = Worksheets.Add
    Elenco.Name = strSheet
    Worksheets("IPSEMA").Range("1:2").Copy Elenco.Range("1:2")

    with

    Worksheets("Master").Copy After:=Worksheets.Count
    Set Elenco = Worksheets(Worksheets.Count)
    Elenco.Name = strSheet
    Elenco.Visible = xlSheetVisible

    This will make a copy of the "Master" sheet, rename it and make it visible.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    Tks and sorry for delay...
    In effect i have in to same wbook other 2 sheet plus the master and the macro only rename one of the existi sheet with the month and year??!!! and not recopy from master....

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    I'm sorry, I don't understand. What is the problem/question?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    What is the macro you're using now? The workbook you attached does not contain this macro.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    this...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    There was an error in the code I posted yesterday. Change the line

    Worksheets("Master").Copy After:=Worksheets.Count

    to

    Worksheets("Master").Copy After:=Worksheets(Worksheets.Count)

    You must remove the code from the module for the Master sheet.

    In the attached file I didn't use the FirstFree function, since it returned an incorrect result.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    Sorry Hans, for delay, tks for code it work greatfull!
    But...
    Assuming i have many sheet GENNAIO_2004, FEBBRAIO_2004 ecc..... generate from the macro, is possible to insert a list box (into sheet controllo) and show into a list all sheet (MONTH_YEAR) and when i select one of this show it?
    Hope you have undresrtand me;-)

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create sheet in base of month... (2000 sr 1)

    If you right-click the navigation buttons to the left of the sheet tabs, Excel will display a list of available worksheets. See screenshot below.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create sheet in base of month... (2000 sr 1)

    This code import a vlue from a txt file and put value into sheet IPSEMA.
    Problem:
    Is possible to refer the variable DATA_DISK make a new sheet named with the name of the mount and insert the related value refered with this variable.
    Example:

    DATA_DISK = 12/12/2005

    create a new into same wbook sheet DICEMBRE and put the values

    DATA_DISK = 12/03/2005

    create a new into same wbook sheet MARZO and put the values

    etc...

    Note:
    Naturally if i import a new file with DATA_DISK = 31/12/2005 ad values into sheet DICEMBRE

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •