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:
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
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:
Code:
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