0

I've been looking on StackOverflow for a solution to this problem and I'm almost there but I can't seem to solve my last problem: saving only a specific worksheet to a new file. Basically, what I want to do is the following:

  1. User clicks and "Archive Data" button
  2. User is prompted to choose a filepath and "SaveAs" a new Excel workbook
  3. Code will copy the range of data in the current worksheet
  4. Code will paste that range to the new Excel workbook specified in the "SaveAs"

My problem is that it saves the whole workbook and I have no way of copying and pasting/saving the specific range in the desired worksheet. Please see the code for reference and let me know if you have any questions.

Sub ArchiveData()

Dim ThisFile As String
Dim NewFile As String
Dim ActBook As Workbook
Dim NewShtName As String
Dim NewFileType As String
NewShtName = "Archived Data on " & Format(Date, "MM.DD.YYYY")

'Copy
ThisFile = ThisWorkbook.FullName
NewFileType = "Excel 1997-2003 (*.xls), *.xls,Excel 2007-2013 (*.xlsx), .*xlsx,Excel 2007-2013 Macro-Enabled (*.xlsm), .*xlsm)"
NewFile = Application.GetSaveAsFilename(InitialFileName:=NewFileName, FileFilter:=NewFileType)

'Paste
If NewFile = "False" Then
    MsgBox ("File unable to be saved")
    Exit Sub
Else
    ActiveWorkbook.Sheets(2).SaveAs Filename:=NewFile, FileFormat:=51 'Need to save as .xls and/or .xlsx
    ThisWorkbook.Sheets(2).range("A4:S65536").Copy
    ActiveWorkbook.Sheets(1).range("A4:S65536").PasteSpecial (xlPasteValues)
    ActiveWorkbook.Sheets(1).Name = NewShtName

    'Close new book
    Set ActBook = ActiveWorkbook
    Workbooks.Open ThisFile
    ActBook.Close

End If
MsgBox ("File saved")
End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
MayankB
  • 15
  • 1
  • 8
  • 1
    If you simply Copy the sheet without specifying any arguments, it will create a new workbook containing just that sheet (that workbook will also be active). You can then save that and do whatever else you need. – Rory Sep 21 '15 at 15:50
  • How would I incorporate the "SaveAs" functionality then? Is there a way to save the worksheet to the workbook specified by the user in the "SaveAs" prompt? – MayankB Sep 21 '15 at 17:44

1 Answers1

0

You would use something like this to copy the sheet to a new workbook, which becomes active, then save it using the path specified by the user:

ActiveWorkbook.Sheets(2).Copy
Activeworkbook.SaveAs Filename:=NewFile, FileFormat:=51

If you don't want the whole sheet, you can use:

Dim wb as Workbook
Set wb = Workbooks.Add(xlwbatworksheet)
ThisWorkbook.Sheets(2).range("A4:S65536").Copy
    wb.Sheets(1).range("A4").PasteSpecial xlPasteValues
wb.saveas Filename:=NewFile, FileFormat:=51
Rory
  • 32,730
  • 5
  • 32
  • 35
  • It still won't work because it says Macro-enabled worksheets cannot be saved as normal Excel files. I feel like this code is still copying the whole workbook and saving as instead of just the range in the specified sheet. – MayankB Sep 21 '15 at 20:03
  • It's copying the whole work**sheet**, not workbook. Does the worksheet contain code? – Rory Sep 21 '15 at 20:18
  • Yes it does. The worksheet has the "Archive" button and thus some VBA associated with it. Is there really no way to only copy a range of data instead of the whole sheet? – MayankB Sep 21 '15 at 20:51