VBA for Button to export 4 worksheets to new workbook, just values and format

[ButterNut]

I am a bit lost, nothing seems to be working right for my code:

Private Sub IntlSaveAsXLSXB_Click()
Application.ScreenUpdating = False
Dim ws As Worksheet
Sheets(Array("Charter-IICE", "High Level Requirements", "IICE +- 50% Summary - INTL")).Copy
For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.Cells.PasteSpecial xlValues
            ws.Cells.PasteSpecial xlFormats
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            ws.Activate
        Next ws

ActiveWorkbook.SaveAs Filename:="GISG-INTL-COST-MODEL-OUTPUT.xlsx", FileFormat:=xlOpenXMLWorkbook

Application.ScreenUpdating = True
End Sub

 

[Marc L]

We are lost too without any crystal clear and complete explanation !

See forum rules …​

 

[ButterNut]

Hi Marc L!
More info...Well I can't share the master .xlsm file as it is proprietary. The gist is that I need to export out 4 worksheets from this monster with the use of a Macro button ("Charter-IICE", "High Level Requirements", "IICE +- 50% Summary - INTL", "Proposal-Financials").
All four sheets have formulas.
The output .xlsx file needs to be clean (Free of Formulas and file link reference to the original master file, while maintaining values and formatting.)

Please let me know if you need more info.

Basic export of 4 tabs to new workbook is not the issue - main problem is in removing the formulas while retaining the values and formatting.

 

[ButterNut]

I will attempt to moch up a dummy file to share.

 

[ButterNut]

The VBA code below works for just exporting the four tabs quickly, but includes formulas, so the data is a mess as most formulas would link to other sheets not part of the export.
need to contain all of the data as values with formatting.

Sample File Attached also.

Private Sub IntlSaveAsXLSXB_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Choose Sheets to Exports
Sheets(Array("Charter-IICE", "High Level Requirements", "IICE +- 50% Summary - INTL", "Proposal-Financials")).Copy

'File name for export/save
ActiveWorkbook.SaveAs Filename:="GISG-INTL-COST-MODEL-OUTPUT.xlsx", FileFormat:=xlOpenXMLWorkbook

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[ButterNut]

Sample file blocked from upload - sorry - will have to work on code alone.

 

[ButterNut]

Suppose I have a workbook1.xlsm with multiple worksheets and full of various formulas. I want to create a new workbook2.xlsx which would maintain the formatting as workbook1 but in all the cells,they would be values instead of formulas. Only looking to export 4 out of 20+ worksheets from workbook1.

The VBA code below gives me the 4 sheets, leaves the formulas on first two worksheets, does a paste special on worksheet 3 and just values on worksheet 4.

My problem lies with Sheet 3 - I have columns with conditional formatting to convert Dollars to Euros and the dollar sign changes from the Euro Dollar sign back to the US Dollar sign. What am I missing? I am so close...

PrivateSub IntlSaveAsXLSX_Click()
Application.ScreenUpdating =False
Application.DisplayAlerts =FalseDim ws As Worksheet
Dim wbNew As Workbook
Worksheets(Array("Charter-IICE","High Level Requirements","IICE +- 50% Summary - INTL","Proposal-Financials")).Copy
Set wbNew = ActiveWorkbookWith wbNew

With.Worksheets(3).UsedRange.Copy.PasteSpecial xlPasteValuesAndNumberFormatsEndWith

With.Worksheets(4).UsedRange.Value =.Value

EndWith

ActiveWorkbook.SaveAs FileName:="GISG-INTL-COST-MODEL-OUTPUT-CD.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.ScreenUpdating =True
Application.DisplayAlerts =True

EndSub