Troubles when Creating workbooks from worksheets.

  • Hello everyone,


    I've got sub that creates workbooks from sheets. My sheets have names in the form of dates in with format yyyy.mm.dd (2021.01.15 - for example).

    Routine does creates files, but they come with names like 2021.01.20 without extensions (it should be 2021.01.20.xlsx) and can not be opened by Excel. When I try to rename them manually, they open with sheets empty, without any data. I would be grateful if you could help me to find out reason for that. Thanks in advance.

    Dilshod


  • Hello,


    A quick fix could be the following :

    Code
    1. Sub NameModif()
    2. Dim ws As Worksheet
    3. For Each ws In ThisWorkbook.Worksheets
    4. If Not ws.Name Like "*T" And ws.Name <> "Sheet1" Then
    5. ws.Name = Replace(ws.Name, ".", "_")
    6. End If
    7. Next ws
    8. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    A quick fix could be the following :

    :)

    Hello Carim,


    Thanks very much for your help. It now does create xlsx files BUT, they still come up empty...

    I've attached sample workbook with table, would you be able to make amendments to the code so that files would come with tables in them?

    Thanks in advance for your help.

    Dilshod

  • Lines 12 to 20 in your macro ...


    below is a tiny modification ... the instruction copying the worksheet comes before the instruction which saves the workbook ...;)


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • If you use ws.Move it will automatically move that sheet to a new workbook, which would simply need renaming.


  • If you use ws.Move it will automatically move that sheet to a new workbook, which would simply need renaming.

    Hi royUK,


    Thanks for your help. The code moved two (out of 30) sheets to new books and gave Automation error at line#23. Apart from that all files with names Book # will have to be renamed manually, when I'll be processing hundreds of sheets it is going to be tedious. Is it possible to give file names the same as sheet names and close them automatically? Thanks again for your help.

    Dilshod

  • The code should be saving as the worksheet's name