VBA to copy worksheets and keep merged cells merged

[cjcass]

I have the following VBA which creates separate files for each worksheet in a workbook and it works fine, however, if any cells are merged in the worksheets the merging is removed when the new file is produced. Is there some code I can put in to keep the merging in tact when the new files are produced? Many thanks.

MyPath = ThisWorkbook.Path
    For Each sht In ThisWorkbook.Sheets
    sht.Copy
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
    ActiveWorkbook.SaveAs _
    Filename:=MyPath & "\" & "Project - " & sht.Name & ".xlsx"
    ActiveWorkbook.Close savechanges:=False
    Next sht
  
    Range("F3").Select

 

[tonyyy]

You might consider the following...

MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
    sht.Copy
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    ActiveWorkbook.SaveAs Filename:=MyPath & "\" & "Project - " & sht.Name & ".xlsx"
    ActiveWorkbook.Close savechanges:=False
Next sht

Cheers,

Tony

 

[cjcass]

You might consider the following...

MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
    sht.Copy
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    ActiveWorkbook.SaveAs Filename:=MyPath & "\" & "Project - " & sht.Name & ".xlsx"
    ActiveWorkbook.Close savechanges:=False
Next sht

Cheers,

Tony

Many thanks for this solution

 

[tonyyy]

You're very welcome.
Please remember to checkmark the question as Solved. Thanks!