Saving A Copy

[Jasper]

I want to save a copy of a spreadsheet but not have the code run in the
copy. The code updates the invoice number on open and clears the
spreadsheet. Then makes a copy on close. The bad part is that it still runs
on the saved copy when you open it. I have tried a few examples from Google
but do not seem to work or do not work right.

here is the code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim SavePath As String
Dim InvoiceNumber As Long

InvoiceNumber = Range("g12").Value
SavePath = "C:\Documents and Settings\"

ThisWorkbook.SaveCopyAs SavePath & InvoiceNumber & " - " &
Format(Date, "yyyy-mm-dd") & " - " & ActiveWorkbook.Name

End Sub

Private Sub Workbook_Open()

Range("g12").Value = Range("g12").Value + 1

Range("b9:b12,b18:b33,a18:a33,f18:f33,c15,e15,g15").Select
Selection.ClearContents

End Sub

 

[GS]

Jasper expressed precisely :

I want to save a copy of a spreadsheet but not have the code run in the copy.
The code updates the invoice number on open and clears the spreadsheet. Then
makes a copy on close. The bad part is that it still runs on the saved copy
when you open it. I have tried a few examples from Google but do not seem to
work or do not work right.

here is the code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim SavePath As String
Dim InvoiceNumber As Long

InvoiceNumber = Range("g12").Value
SavePath = "C:\Documents and Settings\"

ThisWorkbook.SaveCopyAs SavePath & InvoiceNumber & " - " &
Format(Date, "yyyy-mm-dd") & " - " & ActiveWorkbook.Name

End Sub

Private Sub Workbook_Open()

Range("g12").Value = Range("g12").Value + 1

Range("b9:b12,b18:b33,a18:a33,f18:f33,c15,e15,g15").Select
Selection.ClearContents

End Sub

I'm curious as to why your invoice is not a template that you insert
into a "period" workbook. This way, the template is free of the code in
the workbook, AND the code in the workbook only updates the invoice
number when it inserts a new invoice!

 

[Don Guillett]

I want to save a copy of a spreadsheet but not have the code run in the
copy. The code updates the invoice number on open and clears the
spreadsheet. Then makes a copy on close. The bad part is that it still runs
on the saved copy when you open it. I have tried a few examples from Google
but do not seem to work or do not work right.

here is the code.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

        Dim SavePath As String
        Dim InvoiceNumber As Long

        InvoiceNumber = Range("g12").Value
        SavePath = "C:\Documents and Settings\"

            ThisWorkbook.SaveCopyAs SavePath & InvoiceNumber & " - " &
Format(Date, "yyyy-mm-dd") & " - " & ActiveWorkbook.Name

    End Sub

    Private Sub Workbook_Open()

        Range("g12").Value = Range("g12").Value + 1

        Range("b9:b12,b18:b33,a18:a33,f18:f33,c15,e15,g15").Select
        Selection.ClearContents

    End Sub

If you save as .xlsX, all macros are automatically stripped.