save as non macro workbook the delete

[smiler44]

I have a macro enabled workbook that I want to email to someone but I don't want to send it as macro enabled. Using code, I save it as an xlsx workbook but that means I am now using an xlsx workbook and not an xlsm workbook. my code to kill the xlsx workbook wont work.

how can I save my macro enabled workbook as not macro but still have my macro run some subroutines and then delete the non macro work book?

thank you

smiler44

 

[lemo]

save the code in personal.xlsb, perhaps?
i would try that first, see what happens.

 

[smiler44]

I've come up with this, still to try it out but:

1. create the workbook I want to send using

Sub Macro3()
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\Users\Home\Documents\onetodelete.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub


2. copy the work sheets from my xlsm work book to the xlsx work book

Sub Macro2()
Dim fullp As String

Dim dirpath As String ' HR directory path

Dim pth As String 'path including dirpath and subdir
Dim fle As String ' file to delete

dirpath = ActiveWorkbook.Path 'path of file to delete

pth = dirpath & "\"

On Error Resume Next

fle = "onetodelete.xlsx"
fulp = pth + fle
Application.DisplayAlerts = False


Workbooks.Open Filename:=fulp 'onetodelete.xlsx ' opens workbook that will be sent to recipient
Windows("oneimusing.xlsm").Activate
Sheets("sheettotransfer").Select
Sheets("shettotransfer").Copy Before:=Workbooks("onetodelete.xlsx").Sheets(1)

End Sub


3. save and close the onetodelete.xlsx workbook

4. email it across to the recipient

5. kill the onetodelete.xlsx


6 starts again at 1, creates a new workbook each time



smiler44