Form Control Button to save sheet into new workbook in value

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
Hi Experts,

Would be appreciate if anyone could assist in drafting a form control button code.

The form control button should have the following function:

- to save a specific sheet as a new workbook
- will pop up save as dialogue upon clicking the button
- user can determine where to save, and what name to be save
- the excel file saved should be save as normal excel workbook, ie: non-macro workbook
- the source sheet will be in macro based, the button will convert all these into value, but not affecting the column formatting.

Thanks in advance for anyone who could provide their help.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
Hi Experts,

Would be appreciate..

Maybe my explanation is too rough,

basically, i wish for a button on my sheet 1, to save my sheet 2 as a new file, and replace everything as value instead of formula.... hope anyone can help
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,474
Assign this macro to the button.

VBA Code:
Public Sub Save_Sheet_Values_in_XLSX_Workbook()

    Dim xlsxFullName As String
    Dim newWb As Workbook
    
    With Application.FileDialog(msoFileDialogSaveAs)
        .Title = "Save sheet values"
        If .Show Then
            xlsxFullName = .SelectedItems(1)
        Else
            xlsxFullName = ""
        End If
    End With
    
    If xlsxFullName <> "" Then
    
        Set newWb = Workbooks.Add
        ThisWorkbook.Worksheets("Sheet2").Cells.Copy
        newWb.Worksheets(1).Paste
        With newWb.Worksheets(1).UsedRange
            .Value = .Value
        End With
        newWb.Worksheets(1).Name = "Sheet2"
        
        'Suppress warning if new workbook already exists
        Application.DisplayAlerts = False
        
        On Error Resume Next
        newWb.SaveAs xlsxFullName, FileFormat:=xlOpenXMLWorkbook
        newWb.Close SaveChanges:=False
        If Err.Number = 0 Then
            MsgBox "Sheet2 values and formatting saved as " & xlsxFullName, vbInformation
        Else
            MsgBox "Sheet2 not saved", vbExclamation
        End If
        On Error GoTo 0
        
        Application.DisplayAlerts = True
    
    End If
    
End Sub
 

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
Thank You John, it work as intended. Appreciated so much.

Meanwhile, I am not sure if my next request is doable or not, it sound ridiculous to myself actually.

Just wondering, is it possible to fixed the printable area in new workbook which was save from my sheet 2? I notice the new workbook which extracted from my sheet 2 has different print area.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,474

ADVERTISEMENT

Just setting the print area with the macro recorder gives you a starting point for the code. Add this line after the .Name line:
VBA Code:
        newWb.Worksheets(1).PageSetup.PrintArea = ThisWorkbook.Worksheets("Sheet2").PageSetup.PrintArea
Or neater, we can put the block of code after the .Copy line in a With block:
VBA Code:
        With newWb.Worksheets(1)
            .Paste
            .UsedRange.Value = .UsedRange.Value
            .Name = "Sheet2"
            .PageSetup.PrintArea = ThisWorkbook.Worksheets("Sheet2").PageSetup.PrintArea
        End With
 

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
Thanks John for the suggested code, i tried input those codes but it doesn't work unfortunately.

However, i resolve my issue by inputting the "print all sheet into one page" code into your "save as" code, and i got my workbook as what i wanted it to be.

VBA Code:
Application.PrintCommunication = False
        With newWb.Worksheets(1).PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = False
        End With
        Application.PrintCommunication = True

As such, I wish to conclude this is a case resolved hence case closed. Appreciate for all the help again John.

Thank you for such good community to exist.
 

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
Hi John,

I am sorry i have to come back to you.

With regarding to the VBA code, can we add in a code whereby i can default at one saving directory path? ie: default at a document located in my network server whenever i wish to save the file.

i find it troublesome since my saving location will be fix at one location and excel keep prompt me at last save location from other excel file.

Apologies for the inconvenience request.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,645
Messages
5,549,161
Members
410,902
Latest member
G Slim
Top