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:
Set wb = Workbooks.Add
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.
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.
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)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=strFileName _
, FileFormat:=xlCSV, CreateBackup:=False
Note that the macro only transfers over non-hidden cells, and exports to a CSV file. Edit as needed for your purposes.