What I have done (code is listed below):
1) rename and save file automatically (if this is the first time that I am transfering information over - it works. But if the file is already open and I make changes to original and I retransfer the items over - it crashes)
* What I am trying to do is create a new workbook and transfer information from Original worksheet to a New worksheet (This allows others to email this file - trying to make an exact copy). I want the new file to rename (Original file has the word "Backup" in it and New file changes to the word "Memo") automatically save whether it is a brand new workbook never been saved or if it is currently open and changes have been made.
I need some advice!
Current code below!
Public Sub NewWorkBook()
Dim strOriginalName As String
Dim strNewName As String
'This creates a new workbook that copies an existing worksheet with formulas
'to a new worksheet without formulas so it can be emailed out
'Takes activeworkbook file path and puts into a string
Let strOriginalName = ActiveWorkbook.FullName
'Copies all information from Original spread sheet
Range("A1:F39").Copy
'Creates a new workbook
Set newBook = Workbooks.Add
'Pastes Original contents into a new workbook
ActiveSheet.Range("a3").PasteSpecial Paste:=xlValues
'Setting up to change the file name that contains the word "Backup" to "Memo"
ActiveSheet.Range("a1") = strOriginalName
Let ActiveSheet.Range("a2") = ActiveSheet.Range("a1")
ActiveSheet.Range("a2").Replace What:="Backup", Replacement:="Memo", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Let strNewName = ActiveSheet.Range("a2")
ActiveSheet.Rows("1").Hidden = True
ActiveSheet.Rows("2").Hidden = True
