VBA copy worksheet to new file

[Shadkng]

Hi, I would like to copy one tab/sheet form my workbook and have it saved to a new file. Can the file be saved under the tab name and placed in the same directory as the original workbook? It would be OK to keep saving over the previous file. I also need the cell formulas changed to values in the process. After searching I see this is a common need but I haven't come across exactly what I need. The closest I have found is the code below which creates the workbook but doesn't save it - I don't know too much about code so I don't know if this code is efficient. It does change the formulas to values. Any help is appreciated.

Sub SampleMacro()
Dim SrcWB As Workbook, TrgtWB As Workbook
Dim Sh As Worksheet
Dim MyArray As Variant, ShName As Variant
Dim Matched As Boolean
Application.ScreenUpdating = False
MyArray = Array("LABELS") 'Change the sheet names as required
Set SrcWB = ThisWorkbook
Set TrgtWB = Workbooks.Add
Application.CopyObjectsWithCells = False
SrcWB.Worksheets(MyArray).Copy Before:=TrgtWB.Worksheets(1)
Application.CopyObjectsWithCells = True
For Each Sh In TrgtWB.Worksheets
With Cells
.Copy
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
End With
Range("A1").Select
Matched = False
For Each ShName In MyArray
If ShName = Sh.Name Then
Matched = True
Exit For
End If
Next
If Not Matched Then
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End If
Next Sh
Application.CutCopyMode = False
Application.ScreenUpdating = False
End Sub

 

[TotallyConfused]

Correction to my post #20

My line of:
I would think that could also explain why the code stops at that point, since in effect, there is no ‘X’.
Should instead read:
I would think that could also explain why the code stops at that point, since in effect, there is no ‘i’.

Sorry about that.
TotallyConfused

 

[mumps]

@TotallyConfused: Thank you for your observations. You are correct on all counts.
@Shadkng: Without seeing your file, it is hard for me to figure out what is causing the problem. If deleting the extra rows works for you, then delete the rows and then save the file. After saving it, close it and re-open it. You need to do this in order for the row deletion to be permanent. Try the macro again.

 

[Shadkng]

Hi Mumps, yes your advise is good. What I did was have the previous macro (you haven't seen this one) delete the rows before running the macro you wrote that saves the sheet to a file. So far so good. Thanks for you help!

 

[mumps]

My pleasure.