Excel extract sheets (as values) to a new workbook

The Sandbox Software/Technology
1

I have an Excel workbook, Reserve History.xlsm.

In it I have a bunch of input sheets and four output sheets – “GAAP History”, “Stat History”, “Count History”, and “Face Amount History”.

I want to extract those four output sheets into a new workbook and make them values-only.

I’m looking for some vb code that can help me do that.
From VBA Save Sheet as Workbook Excel Macro Code, I got this:

Sub sb_Copy_Save_ActiveSheet_As_Workbook()
    Set wb = Workbooks.Add
    ThisWorkbook.Activate
    ActiveSheet.Copy Before:=wb.Sheets(1)
    wb.Activate
    wb.SaveAs 
    "C:\temp\test3.xlsx"
End Sub

But as far as I can tell, it only does one sheet and doesn’t range value it.

Any direction you can give me would be appreciated.
Regards,
'###

2

I think you want to go more the route of “Paste Values” and “Paste Formats” to a new worksheet in the target workbook and then “manually” changing the new worksheet’s name.

3

This isn’t quite what you’re looking for, but it should be adaptable to your needs:

Sub ExportTab(strTabName As String, strFileName As String)
Sheets(strTabName).Select
Range(“A1”).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add Template:=“Workbook”
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=strFileName _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
End Sub

Note that the macro only transfers over non-hidden cells, and exports to a CSV file. Edit as needed for your purposes.

1 Like