Auto Disable Macros on new sheet

[DDub207]

Hello, I'm a beginner at using Excel macros, and after much cursing I was
finally able to get two macros to run on the same workbook. The one I'm
having trouble with takes the data from a particular cell and uses it to
rename the file before saving it. The problem is, both macros are still
active when opening that new file. This is for invoices. The initial file
is a blank invoice where I want the macros to run. The secondary file is
saved as the customer's name. I do not want the macros to run on the
secondary file. What can be done to auto-disable the macros on the new file?
(These are both auto-run macros.)

Thanks,
Duane

'invoice number advance
Private Sub Workbook_Open()
Range("m3").Value = Range("m3").Value + 0.1
End Sub

'auto filename change
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisFile = Range("D13").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

 

[Jim Rech]

You have to find some way for the macro to distinguish between a workbook
you want to save and one you don't. I don't know what that might be. You
might put a "x" in a way out cell like IV1 to mark an 'old' workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Range("IV1").Value = "" then ''New
Range("IV1").Value = "x" ''Mark as Old
Me.SaveAs Filename:=Range("D13").Value
End if
End Sub


--
Jim
| Hello, I'm a beginner at using Excel macros, and after much cursing I was
| finally able to get two macros to run on the same workbook. The one I'm
| having trouble with takes the data from a particular cell and uses it to
| rename the file before saving it. The problem is, both macros are still
| active when opening that new file. This is for invoices. The initial
file
| is a blank invoice where I want the macros to run. The secondary file is
| saved as the customer's name. I do not want the macros to run on the
| secondary file. What can be done to auto-disable the macros on the new
file?
| (These are both auto-run macros.)
|
| Thanks,
| Duane
|
| 'invoice number advance
| Private Sub Workbook_Open()
| Range("m3").Value = Range("m3").Value + 0.1
| End Sub
|
| 'auto filename change
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| ThisFile = Range("D13").Value
| ActiveWorkbook.SaveAs Filename:=ThisFile
| End Sub
|

 

[Rich J]

if you are basically trying to copy the invoice to a new workbook without the
macros then copy the worksheet to a new workbook and save only the sheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
dim ThisFile, ShName as string

ShName = Activesheet.Name
Sheets(ShName).Select
Sheets(ShName).Copy
ThisFile = Range("D13").Value

ActiveWorkbook.SaveAs Filename:= _
ThisFile, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
' if you want to change the sheet name to the person's name also or create
a new name
Windows(ThisFile).Activate
ActiveSheet.Name = ThisFile
End Sub


I didn't try running this from a BeforeClose routine. You may need to create
a button on your original worksheet and run the routine from there.

 

[DDub207 via OfficeKB.com]

Thanks for your help. It's basically working except for two things. It's
asking if I want to save changes to "new invoice". I would like the blank
invoice, "new invoice" to close automatically without saving changes,
otherwise it won't be blank anymore. Also, it's opening the "customer name
(D13)" file. I would like the new file to save but remain closed. Is there
a way to do this? Also, the last 3 lines of the macro created an error.

Thanks again.

if you are basically trying to copy the invoice to a new workbook without the
macros then copy the worksheet to a new workbook and save only the sheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
dim ThisFile, ShName as string

ShName = Activesheet.Name
Sheets(ShName).Select
Sheets(ShName).Copy
ThisFile = Range("D13").Value

ActiveWorkbook.SaveAs Filename:= _
ThisFile, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
' if you want to change the sheet name to the person's name also or create
a new name
Windows(ThisFile).Activate
ActiveSheet.Name = ThisFile
End Sub

I didn't try running this from a BeforeClose routine. You may need to create
a button on your original worksheet and run the routine from there.

Hello, I'm a beginner at using Excel macros, and after much cursing I was
finally able to get two macros to run on the same workbook. The one I'm

[quoted text clipped - 19 lines]

ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

 

[Rich J]

Try this -

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ThisFile, ShName As String

ShName = ActiveSheet.Name
ThisFile = Range("D13").Value
Sheets(ShName).Select
Sheets(ShName).Copy
ActiveWorkbook.SaveAs Filename:= _
ThisFile, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close ' closes the new single sheet workbook

Application.DisplayAlerts = False ' inhibits the Save message
ActiveWindow.Close ' continues to close the blank invoice

End Sub

 

[DDub207 via OfficeKB.com]

Everything works great. Thanks.

Try this -

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ThisFile, ShName As String

ShName = ActiveSheet.Name
ThisFile = Range("D13").Value
Sheets(ShName).Select
Sheets(ShName).Copy
ActiveWorkbook.SaveAs Filename:= _
ThisFile, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close ' closes the new single sheet workbook

Application.DisplayAlerts = False ' inhibits the Save message
ActiveWindow.Close ' continues to close the blank invoice

End Sub

Thanks for your help. It's basically working except for two things. It's
asking if I want to save changes to "new invoice". I would like the blank

[quoted text clipped - 4 lines]

Thanks again.