Original read only file not closing

G

Gaba

Hello there,
I need to Save As only the visible files from a read only file. When I added
the code to select only visible sheets, the original read only file doesn't
close and it is asking if I want to save the changes (?).
Can anybody please point me to the right direction... How can I make the
read only file to close when the new one is saved?
Thanks so much
Gaba

Private Sub CommandSaveAs_Click()
Unload frmMainMenu
Sheets("Menu").Visible = False
'do not copy menu sheet

Dim NameAk As String
Dim NewName As Variant
Dim myfile As String

Dim bk As Workbook
Dim sh As Worksheet, bReplace As Boolean
Dim sh1 As Worksheet
Set sh1 = ActiveSheet
bReplace = True

For Each sh In Worksheets
If sh.Visible = True Then
sh.Select Replace:=bReplace
bReplace = False
End If
Next
ActiveWindow.SelectedSheets.Copy
Set bk = ActiveWorkbook
Worksheets(1).Select
sh1.Parent.Activate
sh1.Select
bk.Activate

myfile = "Temp"

NameAk = ActiveSheet.Name & myfilename & ".xls"
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\Temp\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")

If NewName <> False Then
If Dir(NewName) <> "" Then
Select Case MsgBox("File Exists. Overwrite ?", vbYesNoCancel +
vbQuestion)
Case vbYes
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=NewName,
FileFormat:=xlWorkbookNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Case vbNo
Do
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\Temp\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")
If NewName = False Then Exit Sub
Loop Until Dir(NewName) = ""
ActiveWorkbook.SaveAs FileName:=NewName,
FileFormat:=xlWorkbookNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Case Else
Exit Sub
End Select
Else
ActiveWorkbook.SaveAs FileName:=NewName,
FileFormat:=xlWorkbookNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

End If
End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top