ioncila
04-20-2010, 07:54 AM
Hi
I have this workbook as a template / master. The first sheet works as an option entering with three buttons - YES, NO and CANCEL.
Before those buttons there's a label saying "Do you wish to create a new file?".
1. If I choose YES, I save it with a new name in a new location, as usual.
2. Choosing NO or CANCEL, it links to a specific sheet in the workbook - to update parts of the existing data.
However, these proccedures bring some risk to unconsciously change the master file.
What I wish to do:
1. Choosing YES, save as a new workbook but WITHOUT THE FIRST SHEET, to prevent similarity between master file and others created from.
2. Choosing NO, links to a specific folder where created files are stored and close master file.
3. Choosing CANCEL, it links to a specific sheet in the master, If I really want to work over the existing data, to update e.g.
What changes do I have to do in this macro?
Sub Caixadetexto3_Clique() 'Create a New File
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
Application.ScreenUpdating = False 'Prevents screen refreshing.
'Save path of the open file
CurrentFile = ThisWorkbook.FullName
NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
"Excel Files 2007 (*.xlsx), *.xlsx," & _
"All files (*.*), *.*"
'Open Save As Window
NewFile = Application.GetSaveAsFilename( _
InitialFileName:=NewFileName, _
fileFilter:=NewFileType)
'Create new file with a new name
If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveAs Filename:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
'Close new file and open master
'Set ActBook = ActiveWorkbook
'Workbooks.Open CurrentFile
'ActBook.Close
End If
'open in a specific sheet
Application.GoTo Reference:=Worksheets("MQT").Range("A1"), Scroll:=True
Application.ScreenUpdating = True
End Sub
Thank you
Ioncila
I have this workbook as a template / master. The first sheet works as an option entering with three buttons - YES, NO and CANCEL.
Before those buttons there's a label saying "Do you wish to create a new file?".
1. If I choose YES, I save it with a new name in a new location, as usual.
2. Choosing NO or CANCEL, it links to a specific sheet in the workbook - to update parts of the existing data.
However, these proccedures bring some risk to unconsciously change the master file.
What I wish to do:
1. Choosing YES, save as a new workbook but WITHOUT THE FIRST SHEET, to prevent similarity between master file and others created from.
2. Choosing NO, links to a specific folder where created files are stored and close master file.
3. Choosing CANCEL, it links to a specific sheet in the master, If I really want to work over the existing data, to update e.g.
What changes do I have to do in this macro?
Sub Caixadetexto3_Clique() 'Create a New File
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
Application.ScreenUpdating = False 'Prevents screen refreshing.
'Save path of the open file
CurrentFile = ThisWorkbook.FullName
NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
"Excel Files 2007 (*.xlsx), *.xlsx," & _
"All files (*.*), *.*"
'Open Save As Window
NewFile = Application.GetSaveAsFilename( _
InitialFileName:=NewFileName, _
fileFilter:=NewFileType)
'Create new file with a new name
If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveAs Filename:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
'Close new file and open master
'Set ActBook = ActiveWorkbook
'Workbooks.Open CurrentFile
'ActBook.Close
End If
'open in a specific sheet
Application.GoTo Reference:=Worksheets("MQT").Range("A1"), Scroll:=True
Application.ScreenUpdating = True
End Sub
Thank you
Ioncila