SaveAs a tab in new Workbook

[sgmpatnaik]

Hello

Good Afternoon

how to SaveAs a particular Tab in a new Workbook with fixed formats, xlValues
i can manage the code but there is one problem that i have some cells are merged so i can't manage that part

here i have a code which is Save As with formulas and fixed formats

Sub SaveSheet()
 
Dim New_file_name
' Written by Barrie Davidson
New_file_name = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls")
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=New_file_name, FileFormat:=xlNormal
ActiveWindow.Close
End Sub

but i want to Save the Sheet with xlValues and Format

kindly help

With Regards

 

[kchiba]

Hi,
Try this
Add the following code just before your ActiveSheet.Copy

Cells.Select
Selection.UnMerge

 

[sgmpatnaik]

@Kachiba

Thanks for your quick response, but the result is not as per my requirement

 

[lohithsriram]

Hi sgmpatnaik,

SaveAs the worksheet Normal as you are doing now (This should bring in the sheet along with formula & formats). Copy all cells and paste as values. Try the below.

Sub SaveSheet()
 
Dim New_file_name
' Written by Barrie Davidson
 
New_file_name = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls")
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=New_file_name, FileFormat:=xlNormal
 
Application.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ActiveWindow.Close
End Sub

 

[sgmpatnaik]

@Lohit

ya got it

it's working nice and cool

Thanks bro, thank Q very much