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

Try the second example on this page
http://www.rondebruin.nl/saveas.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"the_aaron" <(E-Mail Removed)> wrote in message news:5e577db3-bf21-4993-a4b3-(E-Mail Removed)...
> 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

On Jul 31, 4:14*pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> Try the second example on this pagehttp://www.rondebruin.nl/saveas.htm
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> "the_aaron" <the.baker....@gmail.com> wrote in messagenews:5e577db3-bf21-4993-a4b3-(E-Mail Removed)...
> > 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 filewith
> > 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

Works like a charm.

Thanks!!!