Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Save worksheet to new file - values only

  1. #1
    New Member
    Join Date
    Feb 2007
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Save worksheet to new file - values only

    I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database.

    Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.

    I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).

    What modifications do I make to this to get this to work per above requirements?

    Sub CopyMe()
    Dim SaveMeAs As String
    SaveMeAs = Sheets("Sheet1").Range("B2").Text
    Sheets("Sheet3").Copy
    ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
    End Sub

  2. #2
    Board Regular
    Join Date
    Oct 2004
    Posts
    593
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, this may be overkill for you, but this is how I do it. Hope it helps.

    Code:
    Sub SaveValues()
        Dim SourceBook As Workbook, DestBook As Workbook, SourceSheet As Worksheet, DestSheet As Worksheet
        
        Dim SavePath As String, i As Integer
        
        Set SourceBook = ThisWorkbook
        
        '*********************************************
        'Edit next two lines as necessary
        SavePath = "C:\Test\TestSaveValues.xls"
        Set SourceSheet = SourceBook.Sheets("Sheet3")
        '*********************************************
        
        Set DestBook = Workbooks.Add
        Set DestSheet = DestBook.Worksheets.Add
        
        Application.DisplayAlerts = False
        For i = DestBook.Worksheets.Count To 2 Step -1
            DestBook.Worksheets(i).Delete
        Next i
        Application.DisplayAlerts = True
        
        SourceSheet.Cells.Copy
        With DestSheet.Range("A1")
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats 'Delete if you don't want formats copied
        End With
        
        DestSheet.Name = SourceSheet.Name
        
        Application.DisplayAlerts = False 'Delete if you want overwrite warning
        DestBook.SaveAs Filename:=SavePath
        Application.DisplayAlerts = True 'Delete if you delete other line
        
        SavePath = DestBook.FullName
        DestBook.Close 'Delete if you want to leave copy open
        MsgBox ("A copy has been saved to " & SavePath)
        
    End Sub

  3. #3
    New Member
    Join Date
    Feb 2007
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Reid,

    This works great. Thank you! Thank you!

    When the file is saved, I'd like for the file name to be dynamic, based on the contents of a cell (F7 in this case), which currently CONCATENATES a prefix with the contents of another cell.

    Can you tell me what the VBA code is to do this and where do I add it to what you've already provided? The sample code I initially found let me specify the cell reference for the file name.

    When the new worksheet is saved can it be set to have the grid lines and worksheet tabs turned off and, when it is opened in the future, open in full screen mode?

    I appreciate your help. (I'm not proficient in VBA.)

  4. #4
    Board Regular
    Join Date
    Oct 2004
    Posts
    593
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This adds most of the additional functionality. Note that I assume that cell F7 with the filename is on Sheet1. You can change that as you like.

    Implementing Fullscreen mode would be more complicated. Full screen mode affects the instance of the Excel application, not the individual workbooks. In order to do that, you would need to have this procedure enter an workbook_open procedure in the DestBook workbook module to set Application.DisplayFullScreen = True. You probably would want an workbook_BeforeClose procedure to set it to False. This would require the user accepting the macro warning when opening the file.

    Code:
    Sub SaveValues()
        Dim SourceBook As Workbook, DestBook As Workbook, SourceSheet As Worksheet, DestSheet As Worksheet
        
        Dim SavePath As String, i As Integer
        
        Application.ScreenUpdating = False
        
        Set SourceBook = ThisWorkbook
        
        '*********************************************
        'Edit next two lines as necessary
        SavePath = Sheets("Sheet1").Range("F7").Text
        Set SourceSheet = SourceBook.Sheets("Sheet3")
        '*********************************************
        Set DestBook = Workbooks.Add
        Set DestSheet = DestBook.Worksheets.Add
        
        Application.DisplayAlerts = False
        For i = DestBook.Worksheets.Count To 2 Step -1
            DestBook.Worksheets(i).Delete
        Next i
        Application.DisplayAlerts = True
        
        SourceSheet.Cells.Copy
        With DestSheet.Range("A1")
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats 'Delete if you don't want formats copied
        End With
        
        DestSheet.Name = SourceSheet.Name
        DestBook.Activate
        With ActiveWindow
            .DisplayGridlines = False
            .DisplayWorkbookTabs = False
        End With
        SourceBook.Activate
        
        Application.DisplayAlerts = False 'Delete if you want overwrite warning
        DestBook.SaveAs Filename:=SavePath
        Application.DisplayAlerts = True 'Delete if you delete other line
        
        SavePath = DestBook.FullName
        DestBook.Close 'Delete if you want to leave copy open
        MsgBox ("A copy has been saved to " & SavePath)
        
    End Sub

  5. #5
    New Member
    Join Date
    Feb 2007
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    After making the edits to the sheet / cell names, this is working pretty well. I truly appreciate your help.

    The only hitch at this point is that it seems to save the file in which ever folder was last accessed while mucking around in Excel. The file needs to be saved to the network folder: Q:\NCP_Tags. (using a file name from the input cell)

    My attempts to adjust the code and specify the path have been unsuccessful. I've seen other approaches use something as follows:

    Dim SaveName As String
    SaveName = ActiveWorkbook.Sheets(1).Range("A1").Value
    Application.ActiveWorkbook.SaveAs "C:\" & SaveName ' Replace C:\ with the default save path

    But this conflicts with
    DestBook.SaveAs Filename:=SavePath

    If you could provide the code to specify the LAN folder and the file name, I will be over this hurdle and can move to another one. (I have been successfully searching/finding/using bits of code from this and other forums.)

    BTW, I can live w/o forcing full screen mode - just wanted to eliminate some screen clutter for some very inexperienced users.

    Best Regards!

  6. #6
    Board Regular
    Join Date
    Oct 2004
    Posts
    593
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    SavePath in my code serves the same purpose as SaveName in yours

    If you are only going to have the file name in the cell and not the full path, then I would change this line

    Code:
    SavePath = Sheets("Sheet1").Range("F7").Text
    to this

    Code:
    SavePath = "Q:\NCP_Tags\" & Sheets("Sheet1").Range("F7").Text

  7. #7
    New Member
    Join Date
    Feb 2007
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works wonderfully!

    Thank you! Danke! Grazie! Merci! (i.e. a thousand thank you's)

    Given I'm new to VBA and will have to teach my self, can you recommend some good references for starting out?

  8. #8
    Board Regular
    Join Date
    Oct 2004
    Posts
    593
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would buy one of the books. Maybe one of the Mr. Excel books or Excel VBA for dummies by Jon Walkenbach. Walkenbach has a book that a lot of people like called Excell Power Programming with VBA. Reading that book is how I started to learn VBA, but I think that it is pretty heavy going if you do not know anything about programming generally. I have not read the Dummies book, but it gets good marks on Amazon.

    Once you have the basics down, I think hanging out on this board is a great way to learn. You can consider questions that people pose as little projects to work on and compare your approach to the others that are presented. Good luck with it.

  9. #9
    New Member
    Join Date
    Jun 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save worksheet to new file - values only

    I realize that this was posted in 2007, but I came across this the other day and this has really helped me. My question is, I have 4 sheets in a workbook that I would like to save as values. Can this be done?

  10. #10
    Board Regular
    Join Date
    Jun 2015
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save worksheet to new file - values only

    This worked great for me. Thanks !

    I actually ended up using: SavePath = "C:\Temp\" & Sheets("Sheet1").Range("A1").Text & ".xlsx"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •