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

Sub CreateWorkbookFromSheets()

Dim ws As Worksheet
Dim wb As Workbook
Dim path As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

path = "C:\VBA\SMA4\Control Files SMA4\Files with orders"

For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name Like "*T" And ws.Name <> "Sheet1" Then
        Set wb = Workbooks.Add
        wb.SaveAs path & "\" & ws.Name
        ws.Copy before:=wb.Worksheets(1)
        wb.Close savechanges = True
    End If
Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Quote from Carim

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

Sub CreateWorkbookFromSheets()

Dim ws As Worksheet
Dim wb As Workbook
Dim path As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

path = "C:\VBA\SMA4\Control Files SMA4\Files with orders"

For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name Like "*T" And ws.Name <> "Sheet1" Then
        ws.Name = Replace(ws.Name, ".", "_")
        Set wb = Workbooks.Add
        wb.SaveAs path & "\" & ws.Name
        ws.Copy before:=wb.Worksheets(1)
        wb.Close savechanges = True
    End If
Next ws

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Quote from Carim

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

Display More

Now it works perfectly well. Thanks a lot!

Appreciate your help.

Dilshod

Quote from royUK

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

Quote from royUK

The code should be saving as the worksheet's name

Hello royUK,

The code is working perfectly well. I would not be able to solve it on my own. Advantage is that I do not have to rename sheets any more to save files. Thanks for explicit comments in the code. Highly appreciate it.

Dilshod