[B]Help: why generated workbooks that seem excel workbook doesn't support macro?[/B]

[fspino]

Hallo,
first sorry for my English.

I saw that if I copy sheets from a workbook to another new workbook, the last one doesn't preserve the VBA code (it looks like an excel workbook but works like an excel interface).

My code to copy sheet is:

Public Sub CopySheets
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim newbookname As String
    Dim newbooksheets As Integer
    Dim newbooksheetname As String
'   First a little message
    Application.StatusBar = "Wait until the program generates new file..."
'   Then avoid flicker
    Application.ScreenUpdating = False
'   Now create the new file with all the sheets
    With Workbooks.Add(template:=xlWBATWorksheet)
        .Activate
        newbookname = .Name
        newbooksheets = .Sheets.Count
'       Delete all sheets except the first of which we store the name
        Application.DisplayAlerts = False
        For Each ws In .Worksheets
            If ws.Index = 1 Then newbooksheetname = ws.Name Else ws.Delete
        Next ws
        Application.DisplayAlerts = True
'       Copies of all papers found on the new file
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "c" Then
                If ws.Name = "|" Then
                    ws.Copy Before:=.Worksheets(newbooksheetname)
                    .Worksheets(ws.Name).Visible = False
                Else
                    ws.Copy Before:=.Worksheets(newbooksheetname)
                    .Worksheets(ws.Name).Visible = True
                End If
            End If
        Next ws
'       Cancellation of the paper was when creating the file
        Application.DisplayAlerts = False
        .Worksheets(newbooksheetname).Delete
        Application.DisplayAlerts = True
'       Hide Image1 in all sheets that's present
        HideImage1 "ALL"
'       Until the user makes no changes the new workbook is not to save
        .Saved = True
'       Activate "general" sheet (included in copied sheets)
        .Worksheets(ixGenerale).Activate
    End With
'   Update screen
    Application.ScreenUpdating = True
'   Remove the small message
    Application.StatusBar = ""
End Sub

Well, the sheets that I copy contains VBA code and if I look in the VB editor also the new file contains the same VBA code (of course!), but when I save the new workbook and I re-open it the code is disappeared!

Can somebody tell me where is my error?

Thanks in advance.
Francesco

 

[jbeaucaire]

What is the filename you are copying sheets from?
What is the filename you are saving to?

Excel 2007+ has file types that do not allow macros, you need to save as a macro-enabled file type.

 

[fspino]

What is the filename you are copying sheets from?
What is the filename you are saving to?

Excel 2007+ has file types that do not allow macros, you need to save as a macro-enabled file type.

I don't understand your questions...but...
The filename I'm copying sheets is the name of the excel file that contains the sub (see

For Each ws In ThisWorkbook.Worksheets

): in my case "Test.xls"
The filename I'm saving to is a new file (see

With Workbooks.Add(template:=xlWBATWorksheet)

) then when the macro runs it has no name (nothing else than something like Cartel1.xls). When I save it, I give some name like "try.xls"

I use Excel 2003

 

[fspino]