Please help with excel macro.

[Mark F.]

I have a worksheet that is updated with data from a UserForm. I need to
know how to save the worksheet to disk as a backup copy, but not include
any VBA code (e.g., modules, userforms, sheet macros). I do need to
include cell formatting however. Is this possible?

My code...,

' -----------------------------------------------
Private Sub CopyWorksheet()
Dim sFilename As String, sMsg As String

sFilename = "C:\My Documents\backups\" + "AsOf" + Format(Date$,
"mmddyy") + ".xls"
Range("A1:E30").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("E:E").ColumnWidth = 45
Application.CutCopyMode = True
ActiveWorkbook.SaveAs Filename:=sFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.WorkSheets(1).Activate

sMsg = "A backup copy of the worksheet was saved as:" & vbCrLf &
sFilename
MsgBox sMsg, vbInformation, "Worksheet Saved!"
End Sub
' -----------------------------------------------

 

[Ron C]

You could in code (1) create a new workbook, (2) save your worksheet to it, and (3) close the new workbook, saving changes

'(1
Dim strNewWorkbookName As Strin
Workbooks.Ad
strNewWorkbookName = ActiveWorkbook.Nam
'Activate the workbook that contains the worksheet you want to
'save to another workbook
Workbooks("MyWorkbook.xls").Activat

'(2
Sheets("Sheet1").Copy Before:=Workbooks(strNewWorkbookName).Sheets(1
Workbooks(strNewWorkbookName).SaveAs "C:\My Documents\backups\" & strNewWorkbookName 'Or else give it a better, more descriptive name
'(3
Workbooks(strNewWorkbookName).Close Fals

 

[Mark F.]

You could in code (1) create a new workbook, (2) save your worksheet

to it, and (3) close the new workbook, saving changes:

'(1)
Dim strNewWorkbookName As String
Workbooks.Add
strNewWorkbookName = ActiveWorkbook.Name
'Activate the workbook that contains the worksheet you want to
'save to another workbook:
Workbooks("MyWorkbook.xls").Activate

'(2)
Sheets("Sheet1").Copy Before:=Workbooks(strNewWorkbookName).Sheets(1)
Workbooks(strNewWorkbookName).SaveAs "C:\My Documents\backups\" &

strNewWorkbookName 'Or else give it a better, more descriptive name.

'(3)
Workbooks(strNewWorkbookName).Close False

Ron thanks for the advice and the code snippets. I do need to have the
date in the filename so that the other users that read the sheets can
tell which ones are the latest / oldest etc.

Mark