PDA

View Full Version : Save to new workbook and delete a specific sheet



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

ioncila
04-21-2010, 04:39 AM
I think I solved my issue, by searching similar questions.

This is for 1. issue. The 2. and 3. are easily solved with hyperlinks.

If you please take a minute to check it and comprove the solution:

Sub Caixadetexto3_Clique() 'Create 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 new name
If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveAs Filename:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

'Delete "EnterOption" sheet in new file
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name = "EnterOption" Then ws.Delete
Next
Application.DisplayAlerts = True

'delete Module11 in new file
'have to go to Tools>Macro>Security - Trusted Publishers
'and check Trust access to Visual Basic Editor before running the code
'Thanks http://www.ozgrid.com/VBA/delete-module.htm

Set vbCom = Application.VBE.ActiveWorkbook.VBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module11")


'Código seguinte serve para fechar o novo ficheiro e abrir o original
'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


Thanks