Excel 2007 - Export a sheet to a new file

  • Thread starter Thread starter the_aaron
  • Start date Start date
T

the_aaron

Hi,

I have a macro that will export the active sheet to a new file but, it
does not prompt for a file name or a location to safe the file.

I have include the macro below. Can anyone help me modify it so that
it allows me to specify a file name?

Thanks!,
aaron.

Sub Copy_ActiveSheet_New_Workbook()
'Working in Excel 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security
dialog that you
'only see when you copy a sheet from a xlsm file with
macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End If
End With

' 'If you want to change all cells in the worksheet to values,
uncomment these lines.
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False

'Save the new workbook and close it
TempFilePath = Application.DefaultFilePath & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"yyyy-mm-dd hh-mm-ss")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
.Close SaveChanges:=False
End With

MsgBox "You can find the new file in " & TempFilePath

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
 
Back
Top