Save As selection in Active Sheet
 

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 :)

Save As selection in Active Sheet
 

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

Save As selection in Active Sheet
 

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

Save As selection in Active Sheet
 

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

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