Saving a worksheet in a new workbook as values only?

[Cuzzaa]

Hi everyone

Really hope a genius could help me

I'm using the code below in a command button to save a particular worksheet in a new workbook, however I am trying to achieve 2 things, please could someone help me?

1) How do I only paste as values only but while also retaining the formatting of colours, borders etc?
2) Instead of saving in a particular folder by default and saving as "BOM.xlsx", how can I change this to instead prompt the user WHERE to save and WHAT to call the new workbook?

Sub ExporthisBOM()    Dim wb As Workbook
    Set wb = Workbooks.Add
    ThisWorkbook.Sheets("BOM Export").Copy Before:=wb.Sheets(1)
    wb.SaveAs "C:\Users\Folder\BOM.xlsx"
End Sub

Thanks so much for any help and guidance!

 

[mumps]

Try:

Sub ExporthisBOM()
    Dim fName
    Sheets("BOM Export").Copy
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial xlPasteValues
        .Cells(1, 1).PasteSpecial xlPasteFormats
    End With
    Application.CutCopyMode = False
    fName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")
    ActiveWorkbook.SaveAs Filename:=fName
End Sub

 

[Cuzzaa]

Try:

Sub ExporthisBOM()
    Dim fName
    Sheets("BOM Export").Copy
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial xlPasteValues
        .Cells(1, 1).PasteSpecial xlPasteFormats
    End With
    Application.CutCopyMode = False
    fName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")
    ActiveWorkbook.SaveAs Filename:=fName
End Sub

Thank you Mumps!

There is just one thing, when I click on the button now it says 'There is already data here. Do you wish to replace it?' once it opens the new worksheet and pastes the data into it.

Have you any idea why it's doing this and how to make it not do this?

 

[mumps]

Does the file you are saving already exist in the folder that you select and therefore it asks if you want to replace it?

 

[Cuzzaa]

Does the file you are saving already exist in the folder that you select and therefore it asks if you want to replace it?

It's ok I fixed it, it was trying to paste a merged cell and didn't like it so I removed and now it's working as expected.

Just one quick question, is there a way to automatically close the newly saved workbook once you've named it and saved it so it doesn't stay open?

Thanks again mate

 

[mumps]

Try:

Sub ExporthisBOM()
    Dim fName
    Sheets("BOM Export").Copy
    With ActiveSheet
        .UsedRange.Copy
        .Cells(1, 1).PasteSpecial xlPasteValues
        .Cells(1, 1).PasteSpecial xlPasteFormats
    End With
    Application.CutCopyMode = False
    fName = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")
    With ActiveWorkbook
        .SaveAs Filename:=fName
        .Close False
    End With
End Sub

When responding, please click the "Reply" button instead of the "Reply With Quote" button. This helps to remove unnecessary clutter. Thanks.

 

[Cuzzaa]

Thanks so much, and understood.

 

[mumps]

You are very welcome.