Trying to use VBA to save a copy of a workbook

[Guest]

Hello,

I'm trying to use VBA to save a copy of a workbook, I want to just save the
values not all the formulas and links. So I've got the macro to copy the
sheet and then paste special with values only and then I want to Save it. I
want to be prompted where to save it. I've tried using activeworkbook.close
but because I have a workbook before close event macro it just closes without
saving the changes.
Is there anyway I can either stop the before close macro running or can I
get SaveCopyAs to ask for a filename

 

[Chip Pearson]

Try something like

Sub DoSave()

Dim FName As Variant
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName = False Then
' user cancelled.
Exit Sub
Else
If Dir(FName) <> vbNullString Then
' file exists
If MsgBox("File: " & FName & _
" already exists. Overwrite it?", vbYesNo) = vbYes Then
Kill FName
Else
' don't overwrite existing file
Exit Sub
End If
End If
ThisWorkbook.SaveCopyAs Filename:=FName
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Excellent, That works, Thanks a lot

Try something like

Sub DoSave()

Dim FName As Variant
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName = False Then
' user cancelled.
Exit Sub
Else
If Dir(FName) <> vbNullString Then
' file exists
If MsgBox("File: " & FName & _
" already exists. Overwrite it?", vbYesNo) = vbYes Then
Kill FName
Else
' don't overwrite existing file
Exit Sub
End If
End If
ThisWorkbook.SaveCopyAs Filename:=FName
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)