Save worksheet to new file - values only

AAE

New Member
Joined
Feb 19, 2007
Messages
31
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Reid

Well-known Member
Joined
Oct 29, 2004
Messages
593
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
 

AAE

New Member
Joined
Feb 19, 2007
Messages
31
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.)
 

Reid

Well-known Member
Joined
Oct 29, 2004
Messages
593
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
 

AAE

New Member
Joined
Feb 19, 2007
Messages
31

ADVERTISEMENT

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!
 

Reid

Well-known Member
Joined
Oct 29, 2004
Messages
593
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
 

AAE

New Member
Joined
Feb 19, 2007
Messages
31

ADVERTISEMENT

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?
 

Reid

Well-known Member
Joined
Oct 29, 2004
Messages
593
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.
 

jlambert

New Member
Joined
Jun 23, 2014
Messages
1
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?
 

wakerider017

Board Regular
Joined
Jun 10, 2015
Messages
77
This worked great for me. Thanks !

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

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,613
Messages
5,765,422
Members
425,286
Latest member
CazzaBabes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top