| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| save only active sheet Button. | Excel Discussion (Misc queries) | |||
| save active sheet | Excel Programming | |||
| Cell selection in non-active sheet | Excel Programming | |||
| Looping thru the active selection | Excel Programming | |||
| Determining the Selection on a non-active Sheet? | Excel Programming | |||
Linear Mode
