Hi Guys,
I am really new to VBA and I am sure this question has been asked but all the answers I have seen don't quite add up to what I am trying to do...
I can find ways to save the entire workbook and save it to its original folder but I want it to save a COPY of a specific sheet to a specified folder and prompt a message box asking whether it does actually want to save.
All I have managed to do so far is get it to prompt a message but the sheet has already saved.
This is what I have so far:
Sub Save_Job_Sheet()
Dim FName As String
Dim FPath As String
Dim NewBook As Workbook
FPath = "C:\Users\WhyFar"
FName = "Job" & " " & Format(Date, "dd-mm-yy") & ".xlsx"
Set NewBook = Workbooks.Add
ThisWorkbook.Sheets(1).Copy Before:=NewBook.Sheets(1)
If Dir(FPath & "\" & FName) <> "" Then
MsgBox "File " & FPath & "\" & FName & " already exists"
Else
NewBook.SaveAs Filename:=FPath & "\" & FName
End If
' Pops up message
Dim Sure As Integer
If Not ThisWorkbook.Saved Then
If MsgBox("Save File?", vbYesNo, "Saving") = vbYes Then
ThisWorkbook.Save
End If
End If
End Sub
I know the bit that pops up the message is saving the workbook but i can't work out how to change that either?
Cheers
WhyFar
Bookmarks