Form Control Button to save sheet into new workbook in value

[Chaozfate]

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.

 

[Chaozfate]

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]

Assign this macro to the button.

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]

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]

Just setting the print area with the macro recorder gives you a starting point for the code. Add this line after the .Name line:

        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:

        With newWb.Worksheets(1)
            .Paste
            .UsedRange.Value = .UsedRange.Value
            .Name = "Sheet2"
            .PageSetup.PrintArea = ThisWorkbook.Worksheets("Sheet2").PageSetup.PrintArea
        End With

 

[Chaozfate]

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.

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]

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.