VBA to save as XLSM

[p4nny]

Hi, I I would to change the code below to save file as XLSM. When i do that I get message saying choose alternative file extension. Thank you..

Sub exportsheets()

Dim wbNew As Workbook
Dim rngTM As Range
Dim strPath As String

On Error GoTo Errorcatch


Application.ScreenUpdating = False


strPath = "C:\Users\pandoan\Desktop\test\"
Set rngTM = Sheets("Flow TM's").Range("A1")


Do
Sheets(Array("HC pivot TM data", rngTM.Value)).Copy
Set wbNew = ActiveWorkbook
With wbNew
.Sheets("HC pivot TM data").Visible = False
Application.Goto .Sheets(1).Range("B13"), True

Range("F5:G5", "T3:U3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


wbNew.SaveAs strPath & rngTM & Format(Date, "ddmmmyyyy") & "xlsx"

ActiveWorkbook.Close


End With
Set rngTM = rngTM.Offset(1, 0)
Loop Until IsEmpty(ActiveCell) = True

Application.ScreenUpdating = True

Errorcatch:
MsgBox Err.Description

End Sub

 

[Andrew Poulsom]

Does this work for you?

wbNew.SaveAs strPath & rngTM & Format(Date, "ddmmmyyyy") & ".xlsm", FileFormat:=52

 

[p4nny]

great- thank you!! -

When saving into the specified location, how would I overwrite any existing files with the same name? Your help is much appreciated

 

[Andrew Poulsom]

You can try setting Application.DisplayAlerts to False.