Save As selection in Active Sheet
Dave,
Thanks a million. I'll give it a try right now.
--
gaba :)
"Dave Peterson" wrote:
One way:
Option Explicit
Sub SaveFile_As2()
'Save Filename As
'needs to copy all information to new workbook,
'close this one without saving = READ ONLY
Dim NewName As Variant
Dim RngToCopy As Range
Dim newWks As Worksheet
Dim NameAk As String
With ActiveSheet
Set RngToCopy = Selection
End With
NameAk = "e1 Temperature Log" & ".xls"
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")
If NewName < False Then
'create a single sheet workbook and use sheet1
Set newWks = Workbooks.Add(1).Worksheets(1)
RngToCopy.Copy _
Destination:=newWks.Range("a1")
Application.DisplayAlerts = False
newWks.Parent.SaveAs Filename:=NewName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
newWks.Parent.Close savechanges:=False
End If
End Sub
I was kind of confused at why you were saving the original workbook to start,
though.
And this just overwrites any existing file.
gaba wrote:
Dave, Thanks for your answer. I was looking to do it through code, just one
click...
So far, I got this working.
I was curious to see if there was a way to save just the selection.
Sub SaveFile_As()
'Save Filename As
'needs to copy all information to new workbook, close this one without
saving = READ ONLY
Dim NewName As Variant
NameAk = "e1 Temperature Log" & ".xls"
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & _
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
Application.DisplayAlerts = True
Case vbNo
Do
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")
If NewName = False Then Exit Sub
Loop Until Dir(NewName) = ""
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Case Else
Exit Sub
End Select
Else
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=NewName, FileFormat:=xlWorkbookNormal
End If
End If
Range("A2").Select
End Sub
--
gaba :)
"Dave Peterson" wrote:
Create a new workbook (with a single sheet???).
Copy the range to that new workbook's sheet.
Save that new workbook.
gaba wrote:
Hi,
I need to Save As a new file only a selected range (it changes all the
time), right now it saves the whole sheet. How can I save only the selection?
Thanks in advance
--
gaba :)
--
Dave Peterson
--
Dave Peterson
|